Posted by Martin, Feb. 11, 2023, 8:12 p.m.
Question 136 Error
Hi, I got to the same answer in Q136 but it yields an error. Can you please help me?
Here is my script:
WITH base AS(
SELECT
food_order.order_id,
EXTRACT(YEAR FROM food_order.created_at) as year,
EXTRACT(MONTH FROM food_order.created_at) as mon,
TIMESTAMPDIFF(MINUTE, food_order.created_at,food_delivery.delivered_at) AS time_to_deliver
FROM food_order
INNER JOIN food_delivery USING(order_id)
WHERE EXTRACT(YEAR FROM food_order.created_at) = '2021'
),
CTE1 AS(
SELECT
year,
mon,
CASE WHEN time_to_deliver > '60' THEN 1 ELSE 0 END AS late,
COUNT(mon) OVER (PARTITION BY mon) AS monthly_orders
FROM base
)
SELECT
year,
mon,
SUM(late) / AVG(monthly_orders) * 100 AS percent
FROM CTE1
GROUP BY 1,2