Model schema and query new-market readiness
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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:
1) 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%.
2) 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.
3) 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.
Overview: 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.
Read the full DoorDash Data Scientist interview experience this question came from
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:
1) orders_created – total number of orders created on that date.
2) orders_delivered – number of those orders that ended up delivered (cancelled = FALSE and delivered_at IS NOT NULL).
3) orders_cancelled – number of those orders with cancelled = TRUE.
4) 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.
5) 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.
Tables
cities(city_id INT, city_name VARCHAR(100), population INT, launch_date DATE, tz VARCHAR(50))
merchants(merchant_id INT, city_id INT, category VARCHAR(50), is_open BOOLEAN, onboard_date DATE)
couriers(courier_id INT, city_id INT, signup_date DATE, activated_at TIMESTAMP, status VARCHAR(20))
orders(order_id INT, city_id INT, merchant_id INT, courier_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, delivered_at TIMESTAMP, cancelled BOOLEAN, subtotal_cents INT, delivery_fee_cents INT, tip_cents INT)
supply_demand(city_id INT, ts_15min TIMESTAMP, demand_requests INT, active_couriers INT)
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:
1) total_intervals – count of 15-minute intervals with demand_requests > 0.
2) risky_intervals – count of those intervals where active_couriers / demand_requests < 0.8.
3) risk_share – risky_intervals / total_intervals, returned as a DECIMAL rounded to 4 decimal places.
4) 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.
Tables
cities(city_id INT, city_name VARCHAR(100), population INT, launch_date DATE, tz VARCHAR(50))
merchants(merchant_id INT, city_id INT, category VARCHAR(50), is_open BOOLEAN, onboard_date DATE)
couriers(courier_id INT, city_id INT, signup_date DATE, activated_at TIMESTAMP, status VARCHAR(20))
orders(order_id INT, city_id INT, merchant_id INT, courier_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, delivered_at TIMESTAMP, cancelled BOOLEAN, subtotal_cents INT, delivery_fee_cents INT, tip_cents INT)
supply_demand(city_id INT, ts_15min TIMESTAMP, demand_requests INT, active_couriers INT)
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:
1) cohort_city_id – 101.
2) signup_start_date – 2025-08-15.
3) signup_end_date – 2025-08-31.
4) total_couriers – number of couriers in the cohort.
5) activated_within_14_count – number of couriers with activation_within_14 = 1.
6) activated_within_14_rate – activated_within_14_count / total_couriers (NULL if total_couriers = 0).
7) delivered_within_14_count – number of couriers with delivered_within_14 = 1.
8) delivered_within_14_rate – delivered_within_14_count / total_couriers (NULL if total_couriers = 0).
9) 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.
Tables
cities(city_id INT, city_name VARCHAR(100), population INT, launch_date DATE, tz VARCHAR(50))
merchants(merchant_id INT, city_id INT, category VARCHAR(50), is_open BOOLEAN, onboard_date DATE)
couriers(courier_id INT, city_id INT, signup_date DATE, activated_at TIMESTAMP, status VARCHAR(20))
orders(order_id INT, city_id INT, merchant_id INT, courier_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, delivered_at TIMESTAMP, cancelled BOOLEAN, subtotal_cents INT, delivery_fee_cents INT, tip_cents INT)
supply_demand(city_id INT, ts_15min TIMESTAMP, demand_requests INT, active_couriers INT)
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.