Compute rolling cold-delivery rates with windows
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates proficiency with SQL window functions, time-based joins, event deduplication and business-rule implementation for computing rolling metrics over temporal windows.
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.
Tables
orders(order_id INT, customer_id INT, restaurant_id INT, order_ts TIMESTAMP)
deliveries(order_id INT, courier_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, food_temp_c INT, outside_temp_c INT)
complaints(order_id INT, created_ts TIMESTAMP, type TEXT, source TEXT)
restaurants(restaurant_id INT, name TEXT, city TEXT)
Hints
- First, derive a per-delivery cold flag in a CTE using the temperature and an EXISTS() subquery on complaints.
- 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.
Tables
orders(order_id INT, customer_id INT, restaurant_id INT, order_ts TIMESTAMP)
deliveries(order_id INT, courier_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, food_temp_c INT, outside_temp_c INT)
complaints(order_id INT, created_ts TIMESTAMP, type TEXT, source TEXT)
restaurants(restaurant_id INT, name TEXT, city TEXT)
Hints
- Build a per-day per-restaurant aggregate, then CROSS JOIN a generated date series with restaurants so every combination exists.
- 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.
Tables
orders(order_id INT, customer_id INT, restaurant_id INT, order_ts TIMESTAMP)
deliveries(order_id INT, courier_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, food_temp_c INT, outside_temp_c INT)
complaints(order_id INT, created_ts TIMESTAMP, type TEXT, source TEXT)
restaurants(restaurant_id INT, name TEXT, city TEXT)
Hints
- Reuse the per-day per-restaurant aggregates and the 7-day rolling sums from the previous question.
- 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.
Tables
orders(order_id INT, customer_id INT, restaurant_id INT, order_ts TIMESTAMP)
deliveries(order_id INT, courier_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, food_temp_c INT, outside_temp_c INT)
complaints(order_id INT, created_ts TIMESTAMP, type TEXT, source TEXT)
Hints
- First aggregate per courier over the last 7 days to get deliveries_7d and cold_rate_7d using the cold flag logic.
- 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.