Coding questions · DoorDash
Model schema and query new-market readiness
This question evaluates SQL-based data manipulation skills including time-series aggregation, cohort analysis, percentile/median calculations, joins between event and reference tables, and deriving operational KPIs for new-market readiness; it targets the Data Manipulation (SQL/Python) domain for a Data Scientist role.
Assume today is 2025-09-01. You are given (or can propose) a minimal schema to assess new-market readiness and early performance. Use the schema below (feel free to add justified columns if needed) and answer the queries.
Schema (ASCII sample rows included):
cities(city_id, city_name, population, launch_date, tz)
101 | Springfield | 520000 | 2025-08-15 | America/Chicago
102 | Riverton | 180000 | 2025-08-20 | America/Chicago
merchants(merchant_id, city_id, category, is_open, onboard_date)
5001 | 101 | restaurant | true | 2025-08-10
5002 | 101 | grocery | true | 2025-08-12
5003 | 102 | restaurant | false | 2025-08-30
couriers(courier_id, city_id, signup_date, activated_at, status)
9001 | 101 | 2025-08-05 | 2025-08-18 | active
9002 | 101 | 2025-08-20 | null | pending
9003 | 102 | 2025-08-19 | 2025-08-25 | active
orders(order_id, city_id, merchant_id, courier_id, created_at, accepted_at, delivered_at, cancelled, subtotal_cents, delivery_fee_cents, tip_cents)
70001 | 101 | 5001 | 9001 | 2025-08-26 18:02 | 2025-08-26 18:04 | 2025-08-26 18:35 | false | 2800 | 499 | 300
70002 | 101 | 5002 | null | 2025-08-27 12:10 | null | null | true | 1500 | 299 | 0
70003 | 102 | 5003 | 9003 | 2025-08-28 19:20 | 2025-08-28 19:23 | 2025-08-28 20:01 | false | 3200 | 399 | 250
supply_demand(city_id, ts_15min, demand_requests, active_couriers)
101 | 2025-08-26 18:00 | 42 | 35
101 | 2025-08-26 18:15 | 47 | 33
102 | 2025-08-28 19:15 | 28 | 24
Write ANSI SQL for:
- Last 7 days readiness: For city_id = 101, between 2025-08-26 and 2025-09-01 inclusive, compute per-day counts: orders_created, orders_delivered, orders_cancelled, median ETA minutes for delivered orders (delivered_at - created_at), and merchant coverage = active_merchants_per_10k_pop (is_open = true, onboard_date <= date). Return one row per date with a boolean at_risk if (a) median ETA > 35 or (b) cancel_rate > 8%.
- Supply–demand risk: For city_id = 101 over the same window, aggregate by day the share of 15-min intervals where active_couriers / NULLIF(demand_requests,0) < 0.8, and flag days where this share > 0.25.
- Courier activation funnel: For each new courier in city_id = 101 who signed up between 2025-08-15 and 2025-08-31, compute cohort-level rates within 14 days of signup: (i) activated_at present, (ii) completed first delivery, (iii) median time-to-first-delivery (minutes). Return one row with counts and rates.
Daily readiness metrics and at-risk flag for a new city
Using the schema below, write a PostgreSQL query that, for city_id = 101 and the date range from 2025-08-26 to 2025-09-01 inclusive, produces one row per calendar date with daily readiness metrics.
Requirements:
- Consider only orders where city_id = 101 and DATE(created_at) is between 2025-08-26 and 2025-09-01 (inclusive).
- Group all metrics by the order creation date (DATE(created_at)).
- For each date, compute:
- orders_created – total number of orders created on that date.
- orders_delivered – number of those orders that ended up delivered (cancelled = FALSE and delivered_at IS NOT NULL).
- orders_cancelled – number of those orders with cancelled = TRUE.
- median_eta_minutes – the median of (delivered_at - created_at), in minutes, across delivered orders created on that date. If no delivered orders for a date, this value should be NULL.
- merchant_coverage_per_10k – active merchant coverage per 10,000 residents, defined as:
active_merchants_per_10k = active_merchants * 10000 / population,
where active_merchants are merchants with city_id = 101, is_open = TRUE, and onboard_date <= that calendar date. Use the population from the cities table. Return this as a DECIMAL rounded to 4 decimal places.
- Also compute a boolean at_risk column per date:
- Let cancel_rate = orders_cancelled / orders_created for that date. If orders_created = 0, treat cancel_rate as 0 (i.e., the date is not at risk due to cancellations).
- A date is at risk if EITHER median_eta_minutes > 35 OR cancel_rate > 0.08.
- Return one row per calendar date in the range, including dates with zero orders (these should have counts = 0, median_eta_minutes = NULL, merchant_coverage_per_10k based on active merchants as of that date, and at_risk evaluated based on the rules above).
- Order the output by report_date ascending.
Table cities
| column | type | constraints |
|---|
| city_id | INT | PRIMARY KEY, NOT NULL |
| city_name | VARCHAR(100) | NOT NULL |
| population | INT | NOT NULL |
| launch_date | DATE | NOT NULL |
| tz | VARCHAR(50) | NOT NULL |
Sample data:
| city_id | city_name | launch_date | population | tz |
|---|
| 101 | Springfield | 2025-08-15 | 520000 | America/Chicago |
| 102 | Riverton | 2025-08-20 | 180000 | America/Chicago |
Table merchants
| column | type | constraints |
|---|
| merchant_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| is_open | BOOLEAN | NOT NULL |
| onboard_date | DATE | NOT NULL |
Sample data:
| category | city_id | is_open | merchant_id | onboard_date |
|---|
| restaurant | 101 | true | 5001 | 2025-08-10 |
| grocery | 101 | true | 5002 | 2025-08-12 |
| restaurant | 102 | false | 5003 | 2025-08-30 |
Table couriers
| column | type | constraints |
|---|
| courier_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| signup_date | DATE | NOT NULL |
| activated_at | TIMESTAMP | |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| activated_at | city_id | courier_id | signup_date | status |
|---|
| 2025-08-18 09:00:00 | 101 | 9001 | 2025-08-05 | active |
| NULL | 101 | 9002 | 2025-08-20 | pending |
| 2025-08-25 10:00:00 | 102 | 9003 | 2025-08-19 | active |
| 2025-08-26 08:00:00 | 101 | 9004 | 2025-08-25 | active |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| merchant_id | INT | NOT NULL |
| courier_id | INT | |
| created_at | TIMESTAMP | NOT NULL |
| accepted_at | TIMESTAMP | |
| delivered_at | TIMESTAMP | |
| cancelled | BOOLEAN | NOT NULL |
| subtotal_cents | INT | NOT NULL |
| delivery_fee_cents | INT | NOT NULL |
| tip_cents | INT | NOT NULL |
Sample data:
| accepted_at | cancelled | city_id | courier_id | created_at | delivered_at | delivery_fee_cents | merchant_id | order_id | subtotal_cents | tip_cents |
|---|
| 2025-08-26 18:04:00 | false | 101 | 9001 | 2025-08-26 18:02:00 | 2025-08-26 18:35:00 | 499 | 5001 | 70001 | 2800 | 300 |
| NULL | true | 101 | NULL | 2025-08-27 12:10:00 | NULL | 299 | 5002 | 70002 | 1500 | 0 |
| 2025-08-28 19:23:00 | false | 102 | 9003 | 2025-08-28 19:20:00 | 2025-08-28 20:01:00 | 399 | 5003 | 70003 | 3200 | 250 |
| 2025-08-28 23:32:00 | false | 101 | 9004 | 2025-08-28 23:30:00 | 2025-08-29 00:00:00 | 399 | 5001 | 70004 | 2500 | 200 |
Table supply_demand
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| ts_15min | TIMESTAMP | NOT NULL |
| demand_requests | INT | NOT NULL |
| active_couriers | INT | NOT NULL |
Sample data:
| active_couriers | city_id | demand_requests | ts_15min |
|---|
| 35 | 101 | 42 | 2025-08-26 18:00:00 |
| 33 | 101 | 47 | 2025-08-26 18:15:00 |
| 24 | 102 | 28 | 2025-08-28 19:15:00 |
Expected output
One row per calendar date from 2025-08-26 to 2025-09-01 for city_id = 101, including dates with zero orders. Each row shows daily order counts, median delivery ETA in minutes for delivered orders created that day, merchant coverage per 10k population (rounded to 4 decimals), and an at_risk flag based on median ETA and cancel rate.
| at_risk | median_eta_minutes | merchant_coverage_per_10k | orders_cancelled | orders_created | orders_delivered | report_date |
|---|
| false | 33 | 0.0385 | 0 | 1 | 1 | 2025-08-26 |
| true | NULL | 0.0385 | 1 | 1 | 0 | 2025-08-27 |
| false | 30 | 0.0385 | 0 | 1 | 1 | 2025-08-28 |
| false | NULL | 0.0385 | 0 | 0 | 0 | 2025-08-29 |
| false | NULL | 0.0385 | 0 | 0 | 0 | 2025-08-30 |
| false | NULL | 0.0385 | 0 | 0 | 0 | 2025-08-31 |
| false | NULL | 0.0385 | 0 | 0 | 0 | 2025-09-01 |
Hints
- Use a recursive CTE to generate one row per date between 2025-08-26 and 2025-09-01.
- Compute the per-day median ETA with PERCENTILE_CONT over delivered orders grouped by DATE(created_at), and build the cancel_rate in a CASE expression to avoid division by zero.
Daily supply–demand risk from 15-minute intervals
Using the same schema, write an ANSI SQL query to measure supply–demand risk for city_id = 101 over the date range 2025-08-26 to 2025-09-01 inclusive.
Requirements:
- Use the supply_demand table for city_id = 101.
- Consider records where ts_15min is between 2025-08-26 00:00:00 and 2025-09-02 00:00:00 (i.e., all 15-minute intervals whose calendar date is in 2025-08-26 to 2025-09-01).
- Aggregate by calendar date DATE(ts_15min).
- For each date, consider only intervals with demand_requests > 0 when computing the share.
- For each date, compute:
- total_intervals – count of 15-minute intervals with demand_requests > 0.
- risky_intervals – count of those intervals where active_couriers / demand_requests < 0.8.
- risk_share – risky_intervals / total_intervals, returned as a DECIMAL rounded to 4 decimal places.
- supply_demand_at_risk – a boolean that is TRUE if risk_share > 0.25, otherwise FALSE.
- Return one row per date that has at least one interval with demand_requests > 0.
- Order the output by service_date ascending.
Table cities
| column | type | constraints |
|---|
| city_id | INT | PRIMARY KEY, NOT NULL |
| city_name | VARCHAR(100) | NOT NULL |
| population | INT | NOT NULL |
| launch_date | DATE | NOT NULL |
| tz | VARCHAR(50) | NOT NULL |
Sample data:
| city_id | city_name | launch_date | population | tz |
|---|
| 101 | Springfield | 2025-08-15 | 520000 | America/Chicago |
| 102 | Riverton | 2025-08-20 | 180000 | America/Chicago |
Table merchants
| column | type | constraints |
|---|
| merchant_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| is_open | BOOLEAN | NOT NULL |
| onboard_date | DATE | NOT NULL |
Sample data:
| category | city_id | is_open | merchant_id | onboard_date |
|---|
| restaurant | 101 | true | 5001 | 2025-08-10 |
| grocery | 101 | true | 5002 | 2025-08-12 |
| restaurant | 102 | false | 5003 | 2025-08-30 |
Table couriers
| column | type | constraints |
|---|
| courier_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| signup_date | DATE | NOT NULL |
| activated_at | TIMESTAMP | |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| activated_at | city_id | courier_id | signup_date | status |
|---|
| 2025-08-18 09:00:00 | 101 | 9001 | 2025-08-05 | active |
| NULL | 101 | 9002 | 2025-08-20 | pending |
| 2025-08-25 10:00:00 | 102 | 9003 | 2025-08-19 | active |
| 2025-08-26 08:00:00 | 101 | 9004 | 2025-08-25 | active |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| merchant_id | INT | NOT NULL |
| courier_id | INT | |
| created_at | TIMESTAMP | NOT NULL |
| accepted_at | TIMESTAMP | |
| delivered_at | TIMESTAMP | |
| cancelled | BOOLEAN | NOT NULL |
| subtotal_cents | INT | NOT NULL |
| delivery_fee_cents | INT | NOT NULL |
| tip_cents | INT | NOT NULL |
Sample data:
| accepted_at | cancelled | city_id | courier_id | created_at | delivered_at | delivery_fee_cents | merchant_id | order_id | subtotal_cents | tip_cents |
|---|
| 2025-08-26 18:04:00 | false | 101 | 9001 | 2025-08-26 18:02:00 | 2025-08-26 18:35:00 | 499 | 5001 | 70001 | 2800 | 300 |
| NULL | true | 101 | NULL | 2025-08-27 12:10:00 | NULL | 299 | 5002 | 70002 | 1500 | 0 |
| 2025-08-28 19:23:00 | false | 102 | 9003 | 2025-08-28 19:20:00 | 2025-08-28 20:01:00 | 399 | 5003 | 70003 | 3200 | 250 |
| 2025-08-28 23:32:00 | false | 101 | 9004 | 2025-08-28 23:30:00 | 2025-08-29 00:00:00 | 399 | 5001 | 70004 | 2500 | 200 |
Table supply_demand
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| ts_15min | TIMESTAMP | NOT NULL |
| demand_requests | INT | NOT NULL |
| active_couriers | INT | NOT NULL |
Sample data:
| active_couriers | city_id | demand_requests | ts_15min |
|---|
| 35 | 101 | 42 | 2025-08-26 18:00:00 |
| 33 | 101 | 47 | 2025-08-26 18:15:00 |
| 24 | 102 | 28 | 2025-08-28 19:15:00 |
Expected output
One row per date (for dates that have at least one 15-minute interval with demand_requests > 0) showing the total number of demand intervals, how many were under-supplied, the share of under-supplied intervals, and a boolean flag indicating whether risk_share > 0.25.
| risk_share | risky_intervals | service_date | supply_demand_at_risk | total_intervals |
|---|
| 0.5 | 1 | 2025-08-26 | true | 2 |
Hints
- Compute the ratio active_couriers / demand_requests only for rows with demand_requests > 0, and use conditional COUNT expressions to build the numerator and denominator.
- Use a HAVING clause to filter out dates that have no intervals with demand_requests > 0.
Courier activation funnel and time to first delivery
Using the same schema, write an ANSI SQL query to compute a 14-day activation funnel for new couriers in city_id = 101.
Requirements:
- Define the cohort as couriers in city_id = 101 whose signup_date is between 2025-08-15 and 2025-08-31 (inclusive).
- For each courier in this cohort, look at activity within 14 days of signup (i.e., up to signup_date + INTERVAL '14' DAY).
- Define activation_within_14 as 1 if activated_at is NOT NULL and activated_at <= signup_date + 14 days; otherwise 0.
- Define delivered_within_14 as 1 if the courier has at least one completed delivery within 14 days of signup, where a completed delivery is an order with cancelled = FALSE, delivered_at IS NOT NULL, and delivered_at <= signup_date + 14 days; otherwise 0.
- For each courier with delivered_within_14 = 1, define minutes_to_first_delivery as the number of minutes between signup_date (treated as midnight of that date) and the timestamp of the courier's first completed delivery (MIN(delivered_at) among qualifying orders).
- Aggregate to the cohort level and return a single row with:
- cohort_city_id – 101.
- signup_start_date – 2025-08-15.
- signup_end_date – 2025-08-31.
- total_couriers – number of couriers in the cohort.
- activated_within_14_count – number of couriers with activation_within_14 = 1.
- activated_within_14_rate – activated_within_14_count / total_couriers (NULL if total_couriers = 0).
- delivered_within_14_count – number of couriers with delivered_within_14 = 1.
- delivered_within_14_rate – delivered_within_14_count / total_couriers (NULL if total_couriers = 0).
- median_minutes_to_first_delivery – the median of minutes_to_first_delivery across couriers who have a first delivery within 14 days; NULL if no such couriers.
- Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY minutes_to_first_delivery) to compute the median.
- Return exactly one row.
Table cities
| column | type | constraints |
|---|
| city_id | INT | PRIMARY KEY, NOT NULL |
| city_name | VARCHAR(100) | NOT NULL |
| population | INT | NOT NULL |
| launch_date | DATE | NOT NULL |
| tz | VARCHAR(50) | NOT NULL |
Sample data:
| city_id | city_name | launch_date | population | tz |
|---|
| 101 | Springfield | 2025-08-15 | 520000 | America/Chicago |
| 102 | Riverton | 2025-08-20 | 180000 | America/Chicago |
Table merchants
| column | type | constraints |
|---|
| merchant_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| is_open | BOOLEAN | NOT NULL |
| onboard_date | DATE | NOT NULL |
Sample data:
| category | city_id | is_open | merchant_id | onboard_date |
|---|
| restaurant | 101 | true | 5001 | 2025-08-10 |
| grocery | 101 | true | 5002 | 2025-08-12 |
| restaurant | 102 | false | 5003 | 2025-08-30 |
Table couriers
| column | type | constraints |
|---|
| courier_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| signup_date | DATE | NOT NULL |
| activated_at | TIMESTAMP | |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| activated_at | city_id | courier_id | signup_date | status |
|---|
| 2025-08-18 09:00:00 | 101 | 9001 | 2025-08-05 | active |
| NULL | 101 | 9002 | 2025-08-20 | pending |
| 2025-08-25 10:00:00 | 102 | 9003 | 2025-08-19 | active |
| 2025-08-26 08:00:00 | 101 | 9004 | 2025-08-25 | active |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| city_id | INT | NOT NULL |
| merchant_id | INT | NOT NULL |
| courier_id | INT | |
| created_at | TIMESTAMP | NOT NULL |
| accepted_at | TIMESTAMP | |
| delivered_at | TIMESTAMP | |
| cancelled | BOOLEAN | NOT NULL |
| subtotal_cents | INT | NOT NULL |
| delivery_fee_cents | INT | NOT NULL |
| tip_cents | INT | NOT NULL |
Sample data:
| accepted_at | cancelled | city_id | courier_id | created_at | delivered_at | delivery_fee_cents | merchant_id | order_id | subtotal_cents | tip_cents |
|---|
| 2025-08-26 18:04:00 | false | 101 | 9001 | 2025-08-26 18:02:00 | 2025-08-26 18:35:00 | 499 | 5001 | 70001 | 2800 | 300 |
| NULL | true | 101 | NULL | 2025-08-27 12:10:00 | NULL | 299 | 5002 | 70002 | 1500 | 0 |
| 2025-08-28 19:23:00 | false | 102 | 9003 | 2025-08-28 19:20:00 | 2025-08-28 20:01:00 | 399 | 5003 | 70003 | 3200 | 250 |
| 2025-08-28 23:32:00 | false | 101 | 9004 | 2025-08-28 23:30:00 | 2025-08-29 00:00:00 | 399 | 5001 | 70004 | 2500 | 200 |
Table supply_demand
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| ts_15min | TIMESTAMP | NOT NULL |
| demand_requests | INT | NOT NULL |
| active_couriers | INT | NOT NULL |
Sample data:
| active_couriers | city_id | demand_requests | ts_15min |
|---|
| 35 | 101 | 42 | 2025-08-26 18:00:00 |
| 33 | 101 | 47 | 2025-08-26 18:15:00 |
| 24 | 102 | 28 | 2025-08-28 19:15:00 |
Expected output
A single row summarizing the 14-day activation funnel for couriers in city_id = 101 who signed up between 2025-08-15 and 2025-08-31. It includes total cohort size, counts and rates for activation and first delivery within 14 days, and the median minutes from signup to first delivery among couriers who completed a delivery within 14 days.
| activated_within_14_count | activated_within_14_rate | cohort_city_id | delivered_within_14_count | delivered_within_14_rate | median_minutes_to_first_delivery | signup_end_date | signup_start_date | total_couriers |
|---|
| 1 | 0.5 | 101 | 1 | 0.5 | 5760 | 2025-08-31 | 2025-08-15 | 2 |
Hints
- First build a per-courier table that flags activation_within_14 and delivered_within_14 and computes minutes_to_first_delivery using MIN(delivered_at) per courier.
- Compute the median of minutes_to_first_delivery in a separate subquery using PERCENTILE_CONT, then join that median back to the aggregated cohort-level counts and rates.