Write SQL to backtest refund policy
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using the schema and samples below, write a single SQL query (CTEs allowed) that does all of the following for the last 30 days relative to today = 2025-09-01 (i.e., orders placed between 2025-08-02 and 2025-09-01 inclusive): (1) Compute lateness_minutes = GREATEST(0, EXTRACT(EPOCH FROM (del.delivered_at - o.promised_at))/60). (2) Within each (city, delivered_date), compute PERCENT_RANK() of lateness_minutes. (3) For store-days, compute rolling 7-day cold-food refund cost per order using a window (include the current day plus the prior 6 days). (4) Simulate a proposed policy where cold-food refunds are a percentage of order subtotal: lateness <10 min → 0%; 10–29 min → 50%; ≥30 min → 100%; and cap any refund at $50; current policy is 100% of subtotal. (5) Output, by city, the total current refund cost, proposed refund cost, and estimated savings for the period, plus identify the top 5% store-days by current refund cost per order. Use window functions (PARTITION, PERCENT_RANK), a rolling window, subqueries/CTEs, and joins. Assume monetary fields with _cents are integer cents. Provide the final SELECTs producing: (a) city-level aggregates; (b) the list of top 5% store-days with store_id, date, current_cost_per_order, proposed_cost_per_order, savings_per_order.
Schema:
orders(o): order_id INT, customer_id INT, store_id INT, city TEXT, order_placed_at TIMESTAMP, promised_at TIMESTAMP, subtotal_cents INT
deliveries(del): order_id INT, courier_id INT, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, distance_km NUMERIC
refunds(r): refund_id INT, order_id INT, refund_reason TEXT, refund_amount_cents INT, created_at TIMESTAMP
stores(s): store_id INT, city TEXT, cuisine TEXT
couriers(c): courier_id INT, has_thermal_bag BOOLEAN, onboarded_at DATE
Sample rows (minimal, illustrative):
orders
+----------+-------------+----------+------+---------------------+---------------------+----------------+
| order_id | customer_id | store_id | city | order_placed_at | promised_at | subtotal_cents |
+----------+-------------+----------+------+---------------------+---------------------+----------------+
| 1 | 101 | 10 | SF | 2025-08-28 12:00:00 | 2025-08-28 12:40:00 | 3000 |
| 2 | 102 | 10 | SF | 2025-08-28 12:10:00 | 2025-08-28 12:50:00 | 4500 |
| 3 | 103 | 11 | SF | 2025-08-29 19:00:00 | 2025-08-29 19:40:00 | 2000 |
| 4 | 104 | 12 | NYC | 2025-08-29 19:05:00 | 2025-08-29 19:45:00 | 6000 |
| 5 | 105 | 13 | NYC | 2025-08-30 20:00:00 | 2025-08-30 20:35:00 | 2500 |
| 6 | 106 | 12 | NYC | 2025-08-31 18:00:00 | 2025-08-31 18:30:00 | 3500 |
+----------+-------------+----------+------+---------------------+---------------------+----------------+
deliveries
+----------+------------+---------------------+---------------------+-------------+
| order_id | courier_id | picked_up_at | delivered_at | distance_km |
+----------+------------+---------------------+---------------------+-------------+
| 1 | 5001 | 2025-08-28 12:15:00 | 2025-08-28 12:38:00 | 2.1 |
| 2 | 5002 | 2025-08-28 12:30:00 | 2025-08-28 13:35:00 | 7.5 |
| 3 | 5003 | 2025-08-29 19:10:00 | 2025-08-29 20:05:00 | 9.2 |
| 4 | 5004 | 2025-08-29 19:25:00 | 2025-08-29 20:35:00 | 12.3 |
| 5 | 5005 | 2025-08-30 20:05:00 | 2025-08-30 20:50:00 | 3.3 |
| 6 | 5002 | 2025-08-31 18:10:00 | 2025-08-31 19:20:00 | 10.0 |
+----------+------------+---------------------+---------------------+-------------+
refunds
+-----------+----------+-------------+---------------------+--------------------+
| refund_id | order_id | refund_note | created_at | refund_amount_cents|
+-----------+----------+-------------+---------------------+--------------------+
| 9001 | 2 | cold_food | 2025-08-28 13:50:00 | 4500 |
| 9002 | 3 | cold_food | 2025-08-29 20:10:00 | 2000 |
| 9003 | 4 | cold_food | 2025-08-29 20:40:00 | 6000 |
| 9004 | 6 | cold_food | 2025-08-31 19:30:00 | 3500 |
+-----------+----------+-------------+---------------------+--------------------+
stores
+----------+------+---------+
| store_id | city | cuisine |
+----------+------+---------+
| 10 | SF | Burgers |
| 11 | SF | Sushi |
| 12 | NYC | Pizza |
| 13 | NYC | Chinese |
+----------+------+---------+
couriers
+------------+------------------+-------------+
| courier_id | has_thermal_bag | onboarded_at|
+------------+------------------+-------------+
| 5001 | false | 2024-03-01 |
| 5002 | false | 2024-06-15 |
| 5003 | true | 2024-02-10 |
| 5004 | true | 2023-11-20 |
| 5005 | false | 2025-01-05 |
+------------+------------------+-------------+
Notes: Treat missing refunds as zero (no refund). Assume currency is USD; apply the $50 cap to proposed refunds after computing the percentage.
Overview: This question evaluates advanced SQL data-manipulation and analytics competency, including time-based lateness calculations, window functions (PERCENT_RANK and rolling/partitioned windows), CTEs and joins, aggregation, and monetary-field handling for a policy-simulation scenario.
Using the schema and samples below, write a single SQL query (you may use CTEs) that backtests a proposed cold-food refund policy over the last 30 days, assuming 'today' is 2025-06-01. This means you should consider only orders placed between 2025-05-03 and 2025-06-01 inclusive (filter on orders.order_placed_at).
Your query must do all of the following:
1) Join orders to deliveries and refunds and compute lateness_minutes for each order as:
lateness_minutes = GREATEST(0, EXTRACT(EPOCH FROM (del.delivered_at - o.promised_at)) / 60).
2) For each (city, delivered_date), where delivered_date = DATE(del.delivered_at), compute PERCENT_RANK() of lateness_minutes over orders in that (city, delivered_date).
3) For each store-day (store_id, delivered_date), compute:
- number of orders,
- total current cold-food refund cost,
- total proposed cold-food refund cost,
and then a rolling 7-day cold-food refund cost per order using a window over store-days (partition by store_id, ordered by delivered_date, including the current day plus the prior 6 days). The rolling metric should be:
rolling_7d_current_cost_per_order = (sum of current refund cents over the 7-day window) / (sum of orders over the 7-day window), and similarly for the proposed cost.
4) Simulate a proposed policy where cold-food refunds are a percentage of order subtotal_cents based on lateness_minutes:
- lateness < 10 minutes → 0% refund
- 10–29 minutes → 50% of subtotal
- ≥ 30 minutes → 100% of subtotal
After applying the percentage, cap any proposed refund at $50 (i.e., 5000 cents). The current policy is 100% of subtotal; the actual data in refunds represents the current policy. Only orders with a cold_food refund_reason should be treated as cold-food refunds; treat missing refunds as zero (no refund).
5) Produce a final result at the store-day level that includes, for each store_id and delivered_date:
- city,
- city-level total current refund cost over the 30-day period,
- city-level total proposed refund cost over the 30-day period,
- city-level total savings over the 30-day period (current minus proposed),
- store_id,
- delivered_date,
- current refund cost per order for that store-day,
- proposed refund cost per order for that store-day,
- savings per order for that store-day (current per-order minus proposed per-order),
- and a flag indicating whether that store-day is in the top 5% of all store-days (across all cities) by current refund cost per order, using PERCENT_RANK() over store-days.
Assume all *_cents columns are integer cents in USD. Use window functions (including PARTITION BY and PERCENT_RANK), a rolling window over days, subqueries/CTEs, and joins as needed. The final SELECT should return one result set with one row per store-day, including the city-level aggregates repeated for that city and a boolean flag for whether the store-day is in the top 5%.
Tables
orders(order_id INT, customer_id INT, store_id INT, city VARCHAR(50), order_placed_at TIMESTAMP, promised_at TIMESTAMP, subtotal_cents INT)
deliveries(order_id INT, courier_id INT, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, distance_km NUMERIC(6,1))
refunds(refund_id INT, order_id INT, refund_reason VARCHAR(50), refund_amount_cents INT, created_at TIMESTAMP)
stores(store_id INT, city VARCHAR(50), cuisine VARCHAR(50))
couriers(courier_id INT, has_thermal_bag BOOLEAN, onboarded_at DATE)
Hints
- Start by building an order-level CTE that joins orders, deliveries, and cold_food refunds, and computes lateness_minutes.
- Aggregate to store-day for refund sums and order counts, then use window functions both for the 7-day rolling metrics (partition by store_id, ordered by delivered_date) and for identifying the top 5% store-days via PERCENT_RANK on current_cost_per_order.