Coding questions · DoorDash
Write SQL to backtest refund policy
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 (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.
Backtest cold-food refund policy with rolling windows and percentiles
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:
- 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).
- 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).
- 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.
- 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).
- 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%.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| customer_id | INT | NOT NULL |
| store_id | INT | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| order_placed_at | TIMESTAMP | NOT NULL |
| promised_at | TIMESTAMP | NOT NULL |
| subtotal_cents | INT | NOT NULL |
Sample data:
| city | customer_id | order_id | order_placed_at | promised_at | store_id | subtotal_cents |
|---|---|---|---|---|---|---|
| SF | 101 | 1 | 2025-05-28 12:00:00 | 2025-05-28 12:40:00 | 10 | 3000 |
| SF | 102 | 2 | 2025-05-28 12:10:00 | 2025-05-28 12:50:00 | 10 | 4500 |
| SF | 103 | 3 | 2025-05-29 19:00:00 | 2025-05-29 19:40:00 | 11 | 2000 |
| NYC | 104 | 4 | 2025-05-29 19:05:00 | 2025-05-29 19:45:00 | 12 | 6000 |
| NYC | 105 | 5 | 2025-05-30 20:00:00 | 2025-05-30 20:35:00 | 13 | 2500 |
| NYC | 106 | 6 | 2025-05-31 18:00:00 | 2025-05-31 18:30:00 | 12 | 3500 |
Table deliveries
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| courier_id | INT | NOT NULL |
| picked_up_at | TIMESTAMP | NOT NULL |
| delivered_at | TIMESTAMP | NOT NULL |
| distance_km | NUMERIC(6,1) | NOT NULL |
Sample data:
| courier_id | delivered_at | distance_km | order_id | picked_up_at |
|---|---|---|---|---|
| 5001 | 2025-05-28 12:38:00 | 2.1 | 1 | 2025-05-28 12:15:00 |
| 5002 | 2025-05-28 13:35:00 | 7.5 | 2 | 2025-05-28 12:30:00 |
| 5003 | 2025-05-29 20:05:00 | 9.2 | 3 | 2025-05-29 19:10:00 |
| 5004 | 2025-05-29 20:35:00 | 12.3 | 4 | 2025-05-29 19:25:00 |
| 5005 | 2025-05-30 20:50:00 | 3.3 | 5 | 2025-05-30 20:05:00 |
| 5002 | 2025-05-31 19:20:00 | 10 | 6 | 2025-05-31 18:10:00 |
Table refunds
| column | type | constraints |
|---|---|---|
| refund_id | INT | PRIMARY KEY, NOT NULL |
| order_id | INT | NOT NULL |
| refund_reason | VARCHAR(50) | NOT NULL |
| refund_amount_cents | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | order_id | refund_amount_cents | refund_id | refund_reason |
|---|---|---|---|---|
| 2025-05-28 13:50:00 | 2 | 4500 | 9001 | cold_food |
| 2025-05-29 20:10:00 | 3 | 2000 | 9002 | cold_food |
| 2025-05-29 20:40:00 | 4 | 6000 | 9003 | cold_food |
| 2025-05-31 19:30:00 | 6 | 3500 | 9004 | cold_food |
Table stores
| column | type | constraints |
|---|---|---|
| store_id | INT | PRIMARY KEY, NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| cuisine | VARCHAR(50) | NOT NULL |
Sample data:
| city | cuisine | store_id |
|---|---|---|
| SF | Burgers | 10 |
| SF | Sushi | 11 |
| NYC | Pizza | 12 |
| NYC | Chinese | 13 |
Table couriers
| column | type | constraints |
|---|---|---|
| courier_id | INT | PRIMARY KEY, NOT NULL |
| has_thermal_bag | BOOLEAN | NOT NULL |
| onboarded_at | DATE | NOT NULL |
Sample data:
| 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 |
Expected output
One row per store-day (store_id, delivered_date) within 2025-05-03 to 2025-06-01. Each row includes: the city; city-level total current and proposed cold-food refund costs and total savings over the 30-day period; the store-day's current and proposed refund cost per order and savings per order; and a boolean flag indicating whether the store-day is in the top 5% of all store-days by current refund cost per order. On the sample data, there are 5 store-days, and only the NYC store 12 on 2025-05-29 is in the top 5%.
| city | city_total_current_refund_cents | city_total_proposed_refund_cents | city_total_savings_cents | current_cost_per_order | delivered_date | is_top_5pct_store_day | proposed_cost_per_order | savings_per_order | store_id |
|---|---|---|---|---|---|---|---|---|---|
| NYC | 9500 | 9750 | -250 | 6000 | 2025-05-29 | true | 5000 | 1000 | 12 |
| NYC | 9500 | 9750 | -250 | 3500 | 2025-05-31 | false | 3500 | 0 | 12 |
| NYC | 9500 | 9750 | -250 | 0 | 2025-05-30 | false | 1250 | -1250 | 13 |
| SF | 6500 | 5500 | 1000 | 2250 | 2025-05-28 | false | 2250 | 0 | 10 |
| SF | 6500 | 5500 | 1000 | 2000 | 2025-05-29 | false | 1000 | 1000 | 11 |
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.