cswork / libraryOpen practice workspace

Coding questions · DoorDash

Compute rolling cold-delivery rates with windows

This question evaluates proficiency with SQL window functions, time-based joins, event deduplication and business-rule implementation for computing rolling metrics over temporal windows.

Assume a food-delivery platform with the following schema. Use PostgreSQL. A delivery is considered "cold" if food_temp_c < 40 at dropoff OR there is a complaint of type 'cold_food' filed within 2 hours after dropoff. Exclude orders with NULL dropoff_ts. Assume today = 2025-09-01 (so "last 7 days" means 2025-08-26 to 2025-09-01 inclusive). Schema and tiny samples:

orders(order_id INT, customer_id INT, restaurant_id INT, order_ts TIMESTAMP)
+----------+-------------+---------------+---------------------+
| order_id | customer_id | restaurant_id | order_ts |
+----------+-------------+---------------+---------------------+
| 101 | 1 | 10 | 2025-08-26 11:58:00 |
| 102 | 2 | 11 | 2025-08-27 12:10:00 |
| 103 | 3 | 10 | 2025-08-28 18:40:00 |
| 104 | 1 | 12 | 2025-08-31 20:05:00 |
| 105 | 4 | 11 | 2025-09-01 21:15:00 |
+----------+-------------+---------------+---------------------+

deliveries(order_id INT, courier_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, food_temp_c INT, outside_temp_c INT)
+----------+------------+---------------------+---------------------+-------------+----------------+
| order_id | courier_id | pickup_ts | dropoff_ts | food_temp_c | outside_temp_c |
+----------+------------+---------------------+---------------------+-------------+----------------+
| 101 | 201 | 2025-08-26 12:10:00 | 2025-08-26 12:35:00 | 38 | 31 |
| 102 | 202 | 2025-08-27 12:22:00 | 2025-08-27 12:45:00 | 52 | 29 |
| 103 | 201 | 2025-08-28 18:55:00 | 2025-08-28 19:40:00 | 39 | 22 |
| 104 | 203 | 2025-08-31 20:20:00 | 2025-08-31 20:45:00 | 44 | 28 |
| 105 | 202 | 2025-09-01 21:25:00 | 2025-09-01 22:30:00 | 35 | 24 |
+----------+------------+---------------------+---------------------+-------------+----------------+

complaints(order_id INT, created_ts TIMESTAMP, type TEXT, source TEXT)
+----------+---------------------+------------+--------+
| order_id | created_ts | type | source |
+----------+---------------------+------------+--------+
| 101 | 2025-08-26 13:05:00 | cold_food | app |
| 103 | 2025-08-28 20:30:00 | cold_food | web |
| 104 | 2025-09-01 09:00:00 | wrong_item | app |
+----------+---------------------+------------+--------+

restaurants(restaurant_id INT, name TEXT, city TEXT)
+---------------+------------+---------+
| restaurant_id | name | city |
+---------------+------------+---------+
| 10 | Noodle Hut | SF |
| 11 | Taco Loco | SF |
| 12 | Curry Dash | Oakland |
+---------------+------------+---------+

Tasks (use window functions where applicable):

  1. For each date in the last 7 days, compute cold_rate = cold_deliveries / delivered_orders. Count a delivery as cold if either criterion triggers; deduplicate so an order is counted once even if both triggers occur. Return date, delivered_orders, cold_deliveries, cold_rate rounded to 3 decimals.
  2. For each restaurant, compute a 7-day rolling cold_rate ordered by date and return rows for 2025-08-26..2025-09-01 with columns: date, restaurant_id, delivered_orders, cold_deliveries, rolling_cold_rate_7d. Ensure dates with zero volume appear with delivered_orders=0 and rate=NULL.
  3. Rank restaurants by 7-day rolling cold_rate on 2025-09-01 using DENSE_RANK(), breaking ties by higher delivered_orders first. Return top 3 with restaurant_id, name, delivered_orders_7d, cold_rate_7d, rank.
  4. Flag couriers whose cold rate z-score over the last 7 days exceeds +2 relative to the courier population distribution. Return courier_id, deliveries_7d, cold_rate_7d, z_score. Use windowed AVG() and STDDEV_POP() across couriers. Edge cases to handle explicitly in SQL: orders missing in deliveries, multiple complaints per order, NULL food_temp_c (rely only on complaint criterion), and overlapping date boundaries at midnight.

Daily cold-delivery rates over the last 7 days

Using PostgreSQL and the schema below, assume that "today" is 2025-06-01, so the last 7 days are 2025-05-26 to 2025-06-01 inclusive. A delivery is considered cold if food_temp_c < 40 at dropoff OR there is a complaint of type 'cold_food' filed within 2 hours after dropoff. Exclude deliveries with NULL dropoff_ts. For each calendar date in 2025-05-26 to 2025-06-01 (inclusive), compute: delivered_orders, cold_deliveries, and cold_rate = cold_deliveries / delivered_orders rounded to 3 decimal places. Count each order at most once as cold even if both temperature and complaint criteria are true, or if there are multiple complaints. Use the dropoff date (DATE dropoff_ts) to assign a delivery to a day. Handle edge cases: orders missing from deliveries, multiple complaints per order, NULL food_temp_c (rely only on complaints), and NULL dropoff_ts (exclude those rows entirely). Return columns: date, delivered_orders, cold_deliveries, cold_rate.

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
restaurant_idINTNOT NULL
order_tsTIMESTAMPNOT NULL

Sample data:

customer_idorder_idorder_tsrestaurant_id
11012025-05-26 11:58:0010
21022025-05-27 12:10:0011
31032025-05-28 18:40:0010
11042025-05-31 20:05:0012
41052025-06-01 21:15:0011
51062025-05-30 23:50:0010
61072025-05-29 19:30:0012
71082025-05-20 13:00:0011
81092025-05-31 23:55:0010
91102025-05-30 11:00:0011
101112025-05-26 09:15:0012
111122025-05-27 19:30:0010
121132025-05-30 12:30:0011

Table deliveries

columntypeconstraints
order_idINTPRIMARY KEY
courier_idINTNOT NULL
pickup_tsTIMESTAMPNOT NULL
dropoff_tsTIMESTAMP
food_temp_cINT
outside_temp_cINTNOT NULL

Sample data:

courier_iddropoff_tsfood_temp_corder_idoutside_temp_cpickup_ts
2012025-05-26 12:35:0038101312025-05-26 12:10:00
2022025-05-27 12:45:0052102292025-05-27 12:22:00
2012025-05-28 19:40:0039103222025-05-28 18:55:00
2032025-05-31 20:45:0044104282025-05-31 20:20:00
2022025-06-01 22:30:0045105242025-06-01 21:25:00
2042025-05-31 00:20:0045106262025-05-30 23:55:00
2032025-05-29 20:05:00NULL107252025-05-29 19:40:00
202NULL37109242025-05-31 00:10:00
2012025-05-30 11:40:00NULL110272025-05-30 11:10:00
2042025-05-26 09:50:0050111262025-05-26 09:25:00
2052025-05-27 20:05:0050112282025-05-27 19:40:00
2062025-05-30 13:10:0055113302025-05-30 12:40:00

Table complaints

columntypeconstraints
order_idINTNOT NULL
created_tsTIMESTAMPNOT NULL
typeTEXTNOT NULL
sourceTEXTNOT NULL

Sample data:

created_tsorder_idsourcetype
2025-05-26 13:05:00101appcold_food
2025-05-26 13:10:00101phonecold_food
2025-05-28 20:30:00103webcold_food
2025-05-28 20:35:00103appwrong_item
2025-06-01 09:00:00104appwrong_item
2025-06-02 00:45:00105appcold_food
2025-05-31 02:50:00106appcold_food
2025-05-29 23:00:00107appcold_food
2025-05-30 12:00:00110appcold_food

Table restaurants

columntypeconstraints
restaurant_idINTPRIMARY KEY
nameTEXTNOT NULL
cityTEXTNOT NULL

Sample data:

citynamerestaurant_id
SFNoodle Hut10
SFTaco Loco11
OaklandCurry Dash12

Expected output

One row per date from 2025-05-26 to 2025-06-01 inclusive, with total delivered orders (non-NULL dropoff_ts), number of cold deliveries (per the definition, counting each order at most once), and the daily cold_rate rounded to 3 decimals.

cold_deliveriescold_ratedatedelivered_orders
10.52025-05-262
002025-05-272
112025-05-281
002025-05-291
10.52025-05-302
002025-05-312
002025-06-011

Hints

  1. First, derive a per-delivery cold flag in a CTE using the temperature and an EXISTS() subquery on complaints.
  2. Use dropoff_ts::date to group by day, and generate the full 7-day date range with generate_series() so days with no deliveries still appear.

7-day rolling cold rate per restaurant

Using the same PostgreSQL schema and data, and assuming today is 2025-06-01, treat the last 7 days as 2025-05-26 to 2025-06-01 inclusive. A delivery is cold if food_temp_c < 40 at dropoff OR there is a 'cold_food' complaint within 2 hours after dropoff. Exclude deliveries with NULL dropoff_ts. For each restaurant and each date in 2025-05-26 to 2025-06-01, compute: delivered_orders (that day), cold_deliveries (that day), and a 7-day rolling cold rate up to that date (including that date). The 7-day rolling cold rate is (cold deliveries in the last 7 days for that restaurant) / (all deliveries in the last 7 days for that restaurant). Ensure that every restaurant/date combination appears, even if there were no deliveries on that date (delivered_orders = 0, cold_deliveries = 0). For dates where the 7-day window has zero total deliveries for that restaurant, rolling_cold_rate_7d should be NULL. Return columns: date, restaurant_id, delivered_orders, cold_deliveries, rolling_cold_rate_7d.

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
restaurant_idINTNOT NULL
order_tsTIMESTAMPNOT NULL

Sample data:

customer_idorder_idorder_tsrestaurant_id
11012025-05-26 11:58:0010
21022025-05-27 12:10:0011
31032025-05-28 18:40:0010
11042025-05-31 20:05:0012
41052025-06-01 21:15:0011
51062025-05-30 23:50:0010
61072025-05-29 19:30:0012
71082025-05-20 13:00:0011
81092025-05-31 23:55:0010
91102025-05-30 11:00:0011
101112025-05-26 09:15:0012
111122025-05-27 19:30:0010
121132025-05-30 12:30:0011

Table deliveries

columntypeconstraints
order_idINTPRIMARY KEY
courier_idINTNOT NULL
pickup_tsTIMESTAMPNOT NULL
dropoff_tsTIMESTAMP
food_temp_cINT
outside_temp_cINTNOT NULL

Sample data:

courier_iddropoff_tsfood_temp_corder_idoutside_temp_cpickup_ts
2012025-05-26 12:35:0038101312025-05-26 12:10:00
2022025-05-27 12:45:0052102292025-05-27 12:22:00
2012025-05-28 19:40:0039103222025-05-28 18:55:00
2032025-05-31 20:45:0044104282025-05-31 20:20:00
2022025-06-01 22:30:0045105242025-06-01 21:25:00
2042025-05-31 00:20:0045106262025-05-30 23:55:00
2032025-05-29 20:05:00NULL107252025-05-29 19:40:00
202NULL37109242025-05-31 00:10:00
2012025-05-30 11:40:00NULL110272025-05-30 11:10:00
2042025-05-26 09:50:0050111262025-05-26 09:25:00
2052025-05-27 20:05:0050112282025-05-27 19:40:00
2062025-05-30 13:10:0055113302025-05-30 12:40:00

Table complaints

columntypeconstraints
order_idINTNOT NULL
created_tsTIMESTAMPNOT NULL
typeTEXTNOT NULL
sourceTEXTNOT NULL

Sample data:

created_tsorder_idsourcetype
2025-05-26 13:05:00101appcold_food
2025-05-26 13:10:00101phonecold_food
2025-05-28 20:30:00103webcold_food
2025-05-28 20:35:00103appwrong_item
2025-06-01 09:00:00104appwrong_item
2025-06-02 00:45:00105appcold_food
2025-05-31 02:50:00106appcold_food
2025-05-29 23:00:00107appcold_food
2025-05-30 12:00:00110appcold_food

Table restaurants

columntypeconstraints
restaurant_idINTPRIMARY KEY
nameTEXTNOT NULL
cityTEXTNOT NULL

Sample data:

citynamerestaurant_id
SFNoodle Hut10
SFTaco Loco11
OaklandCurry Dash12

Expected output

One row per restaurant per date from 2025-05-26 to 2025-06-01 inclusive. For each restaurant_id and date, show the number of deliveries that day (delivered_orders), the number of those that are cold (cold_deliveries), and the 7-day rolling cold rate up to and including that date. Dates where the 7-day window has zero deliveries for that restaurant have rolling_cold_rate_7d = NULL.

cold_deliveriesdatedelivered_ordersrestaurant_idrolling_cold_rate_7d
12025-05-261101
02025-05-271100.5
12025-05-281100.6667
02025-05-290100.6667
02025-05-300100.6667
02025-05-311100.5
02025-06-010100.5
02025-05-26011NULL
02025-05-271110
02025-05-280110
02025-05-290110
12025-05-302110.3333
02025-05-310110.3333
02025-06-011110.25
02025-05-261120
02025-05-270120
02025-05-280120
02025-05-291120
02025-05-300120
02025-05-311120
02025-06-010120

Hints

  1. Build a per-day per-restaurant aggregate, then CROSS JOIN a generated date series with restaurants so every combination exists.
  2. Use window SUM() OVER (PARTITION BY restaurant_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to compute the 7-day rolling numerator and denominator.

Rank restaurants by 7-day rolling cold rate on 2025-06-01

Using the same PostgreSQL schema, assume today is 2025-06-01, so the last 7 days are 2025-05-26 to 2025-06-01 inclusive. A delivery is cold if food_temp_c < 40 at dropoff OR there is a 'cold_food' complaint within 2 hours after dropoff. Exclude deliveries with NULL dropoff_ts. For each restaurant, compute its 7-day cold statistics up to 2025-06-01: delivered_orders_7d (deliveries between 2025-05-26 and 2025-06-01), and cold_rate_7d = cold_deliveries_7d / delivered_orders_7d. Rank restaurants using DENSE_RANK() by cold_rate_7d in descending order (higher cold_rate_7d = worse performance), breaking ties by higher delivered_orders_7d first. Return the top 3 restaurants with columns: restaurant_id, name, delivered_orders_7d, cold_rate_7d, rank.

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
restaurant_idINTNOT NULL
order_tsTIMESTAMPNOT NULL

Sample data:

customer_idorder_idorder_tsrestaurant_id
11012025-05-26 11:58:0010
21022025-05-27 12:10:0011
31032025-05-28 18:40:0010
11042025-05-31 20:05:0012
41052025-06-01 21:15:0011
51062025-05-30 23:50:0010
61072025-05-29 19:30:0012
71082025-05-20 13:00:0011
81092025-05-31 23:55:0010
91102025-05-30 11:00:0011
101112025-05-26 09:15:0012
111122025-05-27 19:30:0010
121132025-05-30 12:30:0011

Table deliveries

columntypeconstraints
order_idINTPRIMARY KEY
courier_idINTNOT NULL
pickup_tsTIMESTAMPNOT NULL
dropoff_tsTIMESTAMP
food_temp_cINT
outside_temp_cINTNOT NULL

Sample data:

courier_iddropoff_tsfood_temp_corder_idoutside_temp_cpickup_ts
2012025-05-26 12:35:0038101312025-05-26 12:10:00
2022025-05-27 12:45:0052102292025-05-27 12:22:00
2012025-05-28 19:40:0039103222025-05-28 18:55:00
2032025-05-31 20:45:0044104282025-05-31 20:20:00
2022025-06-01 22:30:0045105242025-06-01 21:25:00
2042025-05-31 00:20:0045106262025-05-30 23:55:00
2032025-05-29 20:05:00NULL107252025-05-29 19:40:00
202NULL37109242025-05-31 00:10:00
2012025-05-30 11:40:00NULL110272025-05-30 11:10:00
2042025-05-26 09:50:0050111262025-05-26 09:25:00
2052025-05-27 20:05:0050112282025-05-27 19:40:00
2062025-05-30 13:10:0055113302025-05-30 12:40:00

Table complaints

columntypeconstraints
order_idINTNOT NULL
created_tsTIMESTAMPNOT NULL
typeTEXTNOT NULL
sourceTEXTNOT NULL

Sample data:

created_tsorder_idsourcetype
2025-05-26 13:05:00101appcold_food
2025-05-26 13:10:00101phonecold_food
2025-05-28 20:30:00103webcold_food
2025-05-28 20:35:00103appwrong_item
2025-06-01 09:00:00104appwrong_item
2025-06-02 00:45:00105appcold_food
2025-05-31 02:50:00106appcold_food
2025-05-29 23:00:00107appcold_food
2025-05-30 12:00:00110appcold_food

Table restaurants

columntypeconstraints
restaurant_idINTPRIMARY KEY
nameTEXTNOT NULL
cityTEXTNOT NULL

Sample data:

citynamerestaurant_id
SFNoodle Hut10
SFTaco Loco11
OaklandCurry Dash12

Expected output

Top 3 restaurants ranked by 7-day cold_rate_7d (higher is worse) on 2025-06-01, breaking ties by higher delivered_orders_7d. Includes restaurant name and the 7-day aggregates.

cold_rate_7ddelivered_orders_7dnamerankrestaurant_id
0.54Noodle Hut110
0.254Taco Loco211
03Curry Dash312

Hints

  1. Reuse the per-day per-restaurant aggregates and the 7-day rolling sums from the previous question.
  2. Filter the rolling metrics to date = '2025-06-01', compute cold_rate_7d, then apply DENSE_RANK() OVER (ORDER BY cold_rate_7d DESC, delivered_orders_7d DESC).

Courier cold-rate z-scores over the last 7 days

Using the same PostgreSQL schema and assuming today is 2025-06-01 (last 7 days are 2025-05-26 to 2025-06-01 inclusive), compute for each courier their 7-day cold performance. A delivery is cold if food_temp_c < 40 at dropoff OR there is a 'cold_food' complaint within 2 hours after dropoff. Exclude deliveries with NULL dropoff_ts. For each courier, compute deliveries_7d (number of deliveries with dropoff between 2025-05-26 and 2025-06-01 inclusive), cold_rate_7d = cold_deliveries_7d / deliveries_7d, and then a z-score of cold_rate_7d relative to the distribution of cold_rate_7d values across all couriers. Use AVG() and STDDEV_POP() window functions over the set of couriers to get the mean and standard deviation. Flag only couriers whose z-score > 2 (more than 2 standard deviations above the mean). Return columns: courier_id, deliveries_7d, cold_rate_7d, z_score.

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
restaurant_idINTNOT NULL
order_tsTIMESTAMPNOT NULL

Sample data:

customer_idorder_idorder_tsrestaurant_id
11012025-05-26 11:58:0010
21022025-05-27 12:10:0011
31032025-05-28 18:40:0010
11042025-05-31 20:05:0012
41052025-06-01 21:15:0011
51062025-05-30 23:50:0010
61072025-05-29 19:30:0012
71082025-05-20 13:00:0011
81092025-05-31 23:55:0010
91102025-05-30 11:00:0011
101112025-05-26 09:15:0012
111122025-05-27 19:30:0010
121132025-05-30 12:30:0011

Table deliveries

columntypeconstraints
order_idINTPRIMARY KEY
courier_idINTNOT NULL
pickup_tsTIMESTAMPNOT NULL
dropoff_tsTIMESTAMP
food_temp_cINT
outside_temp_cINTNOT NULL

Sample data:

courier_iddropoff_tsfood_temp_corder_idoutside_temp_cpickup_ts
2012025-05-26 12:35:0038101312025-05-26 12:10:00
2022025-05-27 12:45:0052102292025-05-27 12:22:00
2012025-05-28 19:40:0039103222025-05-28 18:55:00
2032025-05-31 20:45:0044104282025-05-31 20:20:00
2022025-06-01 22:30:0045105242025-06-01 21:25:00
2042025-05-31 00:20:0045106262025-05-30 23:55:00
2032025-05-29 20:05:00NULL107252025-05-29 19:40:00
202NULL37109242025-05-31 00:10:00
2012025-05-30 11:40:00NULL110272025-05-30 11:10:00
2042025-05-26 09:50:0050111262025-05-26 09:25:00
2052025-05-27 20:05:0050112282025-05-27 19:40:00
2062025-05-30 13:10:0055113302025-05-30 12:40:00

Table complaints

columntypeconstraints
order_idINTNOT NULL
created_tsTIMESTAMPNOT NULL
typeTEXTNOT NULL
sourceTEXTNOT NULL

Sample data:

created_tsorder_idsourcetype
2025-05-26 13:05:00101appcold_food
2025-05-26 13:10:00101phonecold_food
2025-05-28 20:30:00103webcold_food
2025-05-28 20:35:00103appwrong_item
2025-06-01 09:00:00104appwrong_item
2025-06-02 00:45:00105appcold_food
2025-05-31 02:50:00106appcold_food
2025-05-29 23:00:00107appcold_food
2025-05-30 12:00:00110appcold_food

Expected output

Couriers whose 7-day cold_rate_7d is more than 2 standard deviations above the mean cold_rate_7d across all couriers, considering dropoffs between 2025-05-26 and 2025-06-01.

cold_rate_7dcourier_iddeliveries_7dz_score
120132.236

Hints

  1. First aggregate per courier over the last 7 days to get deliveries_7d and cold_rate_7d using the cold flag logic.
  2. Then use AVG() and STDDEV_POP() as window functions over all couriers to compute the mean and standard deviation, and filter to z_score > 2.