136. Extremely late orders

easy doordash

  • ***************************************************>=*************************************************************'.
  • *********************************************************************************
  • ****************************************************
  • ************************************************************************************************************
    • ********************************************

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 (
        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;


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

More Doordash questions

ID Title Level FTPR
137 Extremely late first orders doordash medium
138 Happy restaurants doordash easy
139 Poor first delivery experience doordash medium
140 Daily sales of restaurant 100011 doordash hard
141 Cumulative sales of restaurant 100011 doordash hard