Quick Overview

This question evaluates proficiency in complex SQL querying and data manipulation, including joins across tables, time-based filtering, window functions, null and edge-case handling, aggregations, and integrating promotion and order-event data.

Write complex SQL on DoorDash data

Company: DoorDash

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

You are given the following BigQuery-style schema and tiny samples (assume timestamps are UTC; assume promotions.discount_amount is the applied discount). Use today = 2025-09-01 when interpreting “last 7 days.” Write precise SQL answers for (a)–(f), explaining any windowing or edge-case assumptions. customers(customer_id INT, signup_date DATE, city STRING) Sample: customer_id | signup_date | city 1 | 2025-07-10 | SF 2 | 2025-08-01 | SF 3 | 2025-08-20 | NYC merchants(merchant_id INT, name STRING, city STRING, cuisine STRING) Sample: merchant_id | name | city | cuisine 10 | SushiCo | SF | Japanese 11 | TacoTown | SF | Mexican 12 | PizzaBox | NYC | Italian dashers(dasher_id INT, signup_date DATE, vehicle_type STRING) Sample: dasher_id | signup_date | vehicle_type 100 | 2025-06-01 | car 101 | 2025-08-15 | bike promotions(promo_id INT, promo_type STRING, discount_amount NUMERIC, start_date DATE, end_date DATE) Sample: promo_id | promo_type | discount_amount | start_date | end_date 900 | pct10 | 10.00 | 2025-08-01 | 2025-08-31 901 | flat5 | 5.00 | 2025-08-10 | 2025-09-10 orders(order_id INT, customer_id INT, merchant_id INT, dasher_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, canceled_at TIMESTAMP, subtotal NUMERIC, delivery_fee NUMERIC, tip_amount NUMERIC, promo_id INT, status STRING) Sample: order_id | customer_id | merchant_id | dasher_id | created_at | accepted_at | picked_up_at | delivered_at | canceled_at | subtotal | delivery_fee | tip_amount | promo_id | status 5001 | 1 | 10 | 100 | 2025-08-28 12:00 | 2025-08-28 12:02 | 2025-08-28 12:20 | 2025-08-28 12:35 | null | 30.00 | 3.99 | 5.00 | 900 | delivered 5002 | 2 | 11 | null| 2025-08-29 18:05 | null | null | null | 2025-08-29 18:10 | 15.00 | 2.99 | 0.00 | null | canceled 5003 | 1 | 11 | 101 | 2025-08-31 19:00 | 2025-08-31 19:04 | 2025-08-31 19:20 | null | null | 20.00 | 2.99 | 3.00 | 901 | in_progress 5004 | 3 | 12 | 100 | 2025-08-24 11:30 | 2025-08-24 11:31 | null | 2025-08-24 12:10 | null | 22.00 | 4.99 | 0.00 | null | delivered order_events(order_id INT, event_time TIMESTAMP, event_type STRING) Sample: order_id | event_time | event_type 5001 | 2025-08-28 12:00 | created 5001 | 2025-08-28 12:02 | accepted 5001 | 2025-08-28 12:20 | picked_up 5001 | 2025-08-28 12:35 | delivered 5002 | 2025-08-29 18:05 | created 5002 | 2025-08-29 18:10 | canceled 5003 | 2025-08-31 19:00 | created 5003 | 2025-08-31 19:04 | accepted 5004 | 2025-08-24 11:30 | created 5004 | 2025-08-24 11:31 | accepted 5004 | 2025-08-24 12:10 | delivered Tasks: (a) Compute daily on-time delivery rate per city for the last 7 days (2025-08-26 to 2025-09-01). Define on-time as delivered_at <= created_at + INTERVAL 45 MINUTE for non-canceled orders; treat null delivered_at as late. Show city, date, on_time_deliveries, total_eligible, on_time_rate. (b) For August 2025, list top 3 merchants per city by GMV where GMV = subtotal + delivery_fee + tip_amount − COALESCE(promotions.discount_amount,0), excluding canceled orders. Break ties by fewer cancellations, then by merchant_id. Output city, merchant_id, rank_in_city, gmv, cancellations. (c) Using order_events, find orders that have delivered event without a preceding picked_up event (strictly earlier). Return order_id and a minimal event timeline proving the anomaly. (d) Compute weekly dasher acceptance rate: among orders created in a given ISO week and assigned to a dasher, the share with accepted_at within 3 minutes of created_at. Exclude orders canceled before accepted_at. Output dasher_id, week_start_date, assigned_orders, accepted_within_3m, acceptance_rate. (e) Find customers who placed exactly two lifetime orders and both were canceled. Return customer_id and the two canceled_at timestamps in ascending order. (f) For each merchant in August 2025, estimate uplift in average order value (AOV) when a promo is applied vs not applied. Output merchant_id, promo_orders, non_promo_orders, aov_promo, aov_nonpromo, diff. Then describe (briefly) how you would compute a 95% CI for diff via a SQL-based bootstrap or a Python snippet using the same tables.

Overview: This question evaluates proficiency in complex SQL querying and data manipulation, including joins across tables, time-based filtering, window functions, null and edge-case handling, aggregations, and integrating promotion and order-event data.

Read the full DoorDash Data Scientist interview experience this question came from

Top merchants per city by GMV with tie-breakers

For May 2025 (2025-05-01 to 2025-05-31 inclusive), list the top 3 merchants per city by GMV. Definitions: - Exclude canceled orders from GMV. - GMV per order = subtotal + delivery_fee + tip_amount - COALESCE(promotions.discount_amount, 0). - cancellations = number of canceled orders (status = 'canceled') for that merchant during May 2025. Ranking rules (per city): 1) Higher GMV first 2) If tied on GMV, fewer cancellations first 3) If still tied, lower merchant_id first Output columns: - city - merchant_id - rank_in_city (1..3) - gmv - cancellations

Tables

customers(customer_id INT, signup_date DATE, city VARCHAR(50))

merchants(merchant_id INT, name VARCHAR(100), city VARCHAR(50), cuisine VARCHAR(50))

dashers(dasher_id INT, signup_date DATE, vehicle_type VARCHAR(20))

promotions(promo_id INT, promo_type VARCHAR(50), discount_amount DECIMAL(10,2), start_date DATE, end_date DATE)

orders(order_id INT, customer_id INT, merchant_id INT, dasher_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, canceled_at TIMESTAMP, subtotal DECIMAL(10,2), delivery_fee DECIMAL(10,2), tip_amount DECIMAL(10,2), promo_id INT, status VARCHAR(20))

order_events(order_id INT, event_time TIMESTAMP, event_type VARCHAR(30))

Hints

  1. Compute GMV at the order level first (including discount) and then aggregate to merchant.
  2. Use ROW_NUMBER with ORDER BY (gmv DESC, cancellations ASC, merchant_id ASC).

Detect delivered-without-picked-up anomalies from event logs

Using order_events, find orders that have a 'delivered' event but do NOT have a 'picked_up' event strictly earlier than the delivered event. Return: - order_id - created_time (timestamp of created event) - picked_up_time (timestamp of picked_up event; NULL if missing) - delivered_time (timestamp of delivered event) If there are multiple events of the same type, use the earliest timestamp for that event type.

Tables

customers(customer_id INT, signup_date DATE, city VARCHAR(50))

merchants(merchant_id INT, name VARCHAR(100), city VARCHAR(50), cuisine VARCHAR(50))

dashers(dasher_id INT, signup_date DATE, vehicle_type VARCHAR(20))

promotions(promo_id INT, promo_type VARCHAR(50), discount_amount DECIMAL(10,2), start_date DATE, end_date DATE)

orders(order_id INT, customer_id INT, merchant_id INT, dasher_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, canceled_at TIMESTAMP, subtotal DECIMAL(10,2), delivery_fee DECIMAL(10,2), tip_amount DECIMAL(10,2), promo_id INT, status VARCHAR(20))

order_events(order_id INT, event_time TIMESTAMP, event_type VARCHAR(30))

Hints

  1. Aggregate event timestamps per order with conditional `MIN`.
  2. Filter delivered orders where pickup is missing or not earlier than delivery.

Customers with exactly two lifetime orders and both canceled

Find customers who placed exactly two lifetime orders and both orders were canceled. Return: - customer_id - canceled_at_1 (earlier canceled_at, formatted as `YYYY-MM-DD HH24:MI:SS`) - canceled_at_2 (later canceled_at, formatted as `YYYY-MM-DD HH24:MI:SS`) Assume canceled orders have status = 'canceled' and canceled_at is NOT NULL.

Tables

customers(customer_id INT, signup_date DATE, city VARCHAR(50))

merchants(merchant_id INT, name VARCHAR(100), city VARCHAR(50), cuisine VARCHAR(50))

dashers(dasher_id INT, signup_date DATE, vehicle_type VARCHAR(20))

promotions(promo_id INT, promo_type VARCHAR(50), discount_amount DECIMAL(10,2), start_date DATE, end_date DATE)

orders(order_id INT, customer_id INT, merchant_id INT, dasher_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, canceled_at TIMESTAMP, subtotal DECIMAL(10,2), delivery_fee DECIMAL(10,2), tip_amount DECIMAL(10,2), promo_id INT, status VARCHAR(20))

order_events(order_id INT, event_time TIMESTAMP, event_type VARCHAR(30))

Hints

  1. Group by `customer_id` and keep only customers with `COUNT(*) = 2` and two canceled orders.
  2. Use `ARRAY_AGG(canceled_at ORDER BY canceled_at)` to access the earlier and later cancellation timestamps.

Promo vs non-promo AOV uplift per merchant

For each merchant in May 2025 (2025-05-01 to 2025-05-31 inclusive), estimate uplift in average order value (AOV) when a promo is applied vs not applied. Definitions: - Exclude canceled orders. - order_value = subtotal + delivery_fee + tip_amount - COALESCE(promotions.discount_amount, 0) - Promo order: promo_id IS NOT NULL Output columns: - merchant_id - promo_orders - non_promo_orders - aov_promo - aov_nonpromo - diff = aov_promo - aov_nonpromo If a merchant has zero promo orders or zero non-promo orders, the corresponding AOV (and diff) may be NULL.

Tables

customers(customer_id INT, signup_date DATE, city VARCHAR(50))

merchants(merchant_id INT, name VARCHAR(100), city VARCHAR(50), cuisine VARCHAR(50))

dashers(dasher_id INT, signup_date DATE, vehicle_type VARCHAR(20))

promotions(promo_id INT, promo_type VARCHAR(50), discount_amount DECIMAL(10,2), start_date DATE, end_date DATE)

orders(order_id INT, customer_id INT, merchant_id INT, dasher_id INT, created_at TIMESTAMP, accepted_at TIMESTAMP, picked_up_at TIMESTAMP, delivered_at TIMESTAMP, canceled_at TIMESTAMP, subtotal DECIMAL(10,2), delivery_fee DECIMAL(10,2), tip_amount DECIMAL(10,2), promo_id INT, status VARCHAR(20))

order_events(order_id INT, event_time TIMESTAMP, event_type VARCHAR(30))

Hints

  1. Compute order_value once (including discount) and then average it conditionally by promo_id IS NULL/NOT NULL.
  2. NULL AOVs are expected if a merchant has no orders in one of the two groups.

Loading coding console...