- ***************************************************>=) *************************************************************'.
- *********************************************************************************
- ****************************************************
- ************************************************************************************************************
-
********************************************
-
Table 1: food_delivery
col_name | col_type ---------------+------------------- order_id | bigint courier_id | bigint delivered_at | timestamp rating | int (1,...,5)
Table 2: food_order
Food orders that are placed on Doordash.
col_name | col_type ---------------+------------------- order_id | bigint restaurant_id | bigint customer_id | bigint created_at | timestamp total_amt | float
Sample results
year | mon | percent ------+-----+--------------------- 2021 | 7 | 23.0956239870340357 2021 | 8 | 24.9605055292259084
Solution postgres
WITH delivery_time AS (
SELECT
O.order_id,
EXTRACT(YEAR FROM created_at) AS year,
EXTRACT(MON FROM created_at) AS mon,
EXTRACT (EPOCH FROM delivered_at - created_at)::int/60 AS minutes
FROM food_order O
INNER JOIN food_delivery D
ON D.order_id = O.order_id
)
SELECT year, mon, COUNT(CASE WHEN minutes >= 60 THEN order_id ELSE NULL END) * 100.0 / COUNT(*) AS percent
FROM delivery_time
GROUP BY year, mon
ORDER BY year, mon;
Explanation
This query is used to calculate the percentage of orders that took longer than 60 minutes to be delivered each month, for each year.
The query begins by creating a temporary table called "delivery_time" using a common table expression (CTE). This table includes the order ID, year, month, and the time it took for each order to be delivered in minutes.
The main query then selects the year, month, and the percentage of orders that took longer than 60 minutes to be delivered. It does this by first filtering the orders that took longer than 60 minutes using a CASE statement and then dividing the count of those orders by the total number of orders for that month and year. The result is then multiplied by 100 to get a percentage.
The final output is grouped by year and month, and ordered by year and month to display the results chronologically.
Expected results