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.
Quick Answer: 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.