Quick Overview

This question evaluates proficiency with analytical SQL and data engineering concepts, including CTE design, window functions (LAG/QUALIFY), timezone-aware timestamp handling, aggregation and rate calculation, per-order interval computation, and distributional comparison (median).

Write SQL for cold-complaint diagnostics with LAG/QUALIFY

Company: DoorDash

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Using BigQuery/Snowflake-style SQL (CTEs required; use LAG and QUALIFY), answer the tasks below. Assume 'today' is 2025-09-01. Schema and small samples: Tables: - deliveries(delivery_id STRING, order_id STRING, courier_id STRING, restaurant_id STRING, city STRING, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km FLOAT, status STRING) - complaints(complaint_id STRING, order_id STRING, complaint_ts TIMESTAMP, type STRING) - order_events(order_id STRING, event_ts TIMESTAMP, status STRING) -- status in ('ready','picked_up','delivered') - couriers(courier_id STRING, city STRING, has_insulated_bag BOOLEAN, activation_date DATE) Samples (ASCII): - deliveries delivery_id | order_id | courier_id | restaurant_id | city | pickup_ts | dropoff_ts | distance_km | status D1 | 1001 | C1 | R1 | SF | 2025-09-01 12:05:00 | 2025-09-01 12:25:00 | 3.2 | completed D2 | 1002 | C2 | R2 | SF | 2025-09-01 12:10:00 | 2025-09-01 12:55:00 | 7.8 | completed D3 | 1003 | C1 | R3 | NYC | 2025-08-31 19:40:00 | 2025-08-31 20:20:00 | 5.1 | completed D4 | 1004 | C3 | R4 | NYC | 2025-08-31 19:50:00 | 2025-08-31 20:10:00 | 2.0 | completed D5 | 1005 | C2 | R2 | SF | 2025-08-31 12:00:00 | 2025-08-31 12:40:00 | 6.3 | completed - complaints complaint_id | order_id | complaint_ts | type A1 | 1002 | 2025-09-01 13:10:00 | cold A2 | 1003 | 2025-08-31 21:00:00 | cold A3 | 1004 | 2025-08-31 20:45:00 | late - order_events order_id | event_ts | status 1001 | 2025-09-01 12:00:00 | ready 1001 | 2025-09-01 12:05:00 | picked_up 1001 | 2025-09-01 12:25:00 | delivered 1002 | 2025-09-01 12:35:00 | ready 1002 | 2025-09-01 12:40:00 | picked_up 1002 | 2025-09-01 12:55:00 | delivered 1003 | 2025-08-31 19:20:00 | ready 1003 | 2025-08-31 19:40:00 | picked_up 1003 | 2025-08-31 20:20:00 | delivered 1004 | 2025-08-31 19:30:00 | ready 1004 | 2025-08-31 19:50:00 | picked_up 1004 | 2025-08-31 20:10:00 | delivered - couriers courier_id | city | has_insulated_bag | activation_date C1 | SF | TRUE | 2025-06-01 C2 | SF | FALSE | 2025-07-15 C3 | NYC | TRUE | 2025-03-10 Assumptions: - Consider only deliveries.status = 'completed'. Count at most one 'cold' complaint per order, within 24h of dropoff. Tasks (write one SQL script with CTEs): A) Compute city-day cold complaint rate for the last 30 days ending 2025-09-01 (complaints per completed delivery). Return city, date, deliveries, cold_complaints, complaint_rate. Ensure time zones are handled by casting dropoff_ts to city-local date; state any assumption you make for timezone. B) Using order_events and LAG over (PARTITION BY order_id ORDER BY event_ts), compute per order: ready_to_pickup_min and pickup_to_dropoff_min. Then, for 2025-08-01 to 2025-09-01, compare median pickup_to_dropoff_min between orders with a cold complaint vs. without, per city. Keep only city rows where the difference > 12 minutes using QUALIFY on a window over cities. C) For each courier, consider their last 100 completed deliveries up to 2025-09-01 23:59:59 in their city. Flag couriers with cold complaint rate > 2× the city median in the same period and at least 100 deliveries. Use QUALIFY to (i) pick the last 100 by dropoff_ts per courier and (ii) filter the outliers. Return courier_id, city, deliveries, cold_complaints, rate, city_median_rate. D) Compute, per city, the P90 of pickup_to_dropoff_min. Then, for each courier, compute the share of their deliveries exceeding their city’s P90, and QUALIFY the top 5 couriers per city by that share (ties broken by higher deliveries). Explain any tie-breaking window you use.

Overview: This question evaluates proficiency with analytical SQL and data engineering concepts, including CTE design, window functions (LAG/QUALIFY), timezone-aware timestamp handling, aggregation and rate calculation, per-order interval computation, and distributional comparison (median).

City-day cold complaint rate over the last 30 days

Using BigQuery/Snowflake-style SQL with CTEs, compute the city-day cold complaint rate over the 30 days from 2025-05-03 through 2025-06-01 (inclusive). Consider only deliveries with status = 'completed'. Count at most one 'cold' complaint per order, and only if the complaint occurs within 24 hours after the delivery's dropoff_ts. Assume all timestamps are already stored in the corresponding city’s local timezone, so you can obtain the city-local date by CAST(dropoff_ts AS DATE). Return one row per city and dropoff date that has at least one completed delivery, with the following columns: - city - date (the local date derived from dropoff_ts) - deliveries (number of completed deliveries on that city-date) - cold_complaints (number of orders on that city-date with at least one qualifying cold complaint) - complaint_rate (cold_complaints / deliveries as a numeric rate)

Tables

deliveries(delivery_id VARCHAR(20), order_id VARCHAR(20), courier_id VARCHAR(20), restaurant_id VARCHAR(20), city VARCHAR(50), pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), status VARCHAR(20))

complaints(complaint_id VARCHAR(20), order_id VARCHAR(20), complaint_ts TIMESTAMP, type VARCHAR(20))

order_events(order_id VARCHAR(20), event_ts TIMESTAMP, status VARCHAR(20))

couriers(courier_id VARCHAR(20), city VARCHAR(50), has_insulated_bag BOOLEAN, activation_date DATE)

Hints

  1. Join deliveries to complaints on order_id and restrict complaints to the 24 hours after dropoff_ts and type = 'cold'.
  2. Aggregate by city and CAST(dropoff_ts AS DATE); compute the rate as cold_complaints * 1.0 / NULLIF(deliveries, 0).

Median pickup-to-dropoff time vs cold complaints by city

Using PostgreSQL with CTEs and window functions, analyze delivery duration differences for cold complaints. 1. From order_events, compute for each order_id two durations in minutes: - ready_to_pickup_min: time from the 'ready' event to the next 'picked_up' event - pickup_to_dropoff_min: time from the 'picked_up' event to the next 'delivered' event Use LAG over (PARTITION BY order_id ORDER BY event_ts) to derive the previous event timestamp. 2. Keep completed deliveries whose dropoff_ts falls from 2025-05-01 through 2025-06-01, inclusive. Classify each order as having a cold complaint when complaints.type = 'cold' occurs within 24 hours after that order's dropoff_ts. Treat multiple cold complaints for the same order as a single flag. 3. For each city, compute the median pickup_to_dropoff_min separately for orders with a cold complaint and orders without a cold complaint. 4. Return one row per city with: - city - median_cold - median_no_cold - diff_minutes = ABS(median_cold - median_no_cold) Keep only cities where diff_minutes > 12 minutes. Order by city.

Tables

deliveries(delivery_id VARCHAR(20), order_id VARCHAR(20), courier_id VARCHAR(20), restaurant_id VARCHAR(20), city VARCHAR(50), pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), status VARCHAR(20))

complaints(complaint_id VARCHAR(20), order_id VARCHAR(20), complaint_ts TIMESTAMP, type VARCHAR(20))

order_events(order_id VARCHAR(20), event_ts TIMESTAMP, status VARCHAR(20))

couriers(courier_id VARCHAR(20), city VARCHAR(50), has_insulated_bag BOOLEAN, activation_date DATE)

Hints

  1. Use LAG(event_ts) within each order_id to measure transitions between ready, picked_up, and delivered events.
  2. In PostgreSQL, convert timestamp differences to minutes with EXTRACT(EPOCH FROM (later_ts - earlier_ts)) / 60.0.

Courier-level cold complaint outliers vs city median

Using BigQuery/Snowflake-style SQL with CTEs and QUALIFY, flag couriers whose recent cold complaint rate is unusually high. For each courier, consider their last 100 completed deliveries with dropoff_ts up to 2025-06-01 23:59:59 (inclusive). "Last" is determined by dropoff_ts in descending order. Use QUALIFY with a window function to select at most the 100 most recent deliveries per courier. For those last-100 deliveries per courier: - Mark each delivery as having a cold complaint (1) or not (0), where a cold complaint is complaints.type = 'cold' within 24 hours after dropoff_ts (at most one cold complaint per order). - For each (courier_id, city), compute: - deliveries (number of considered deliveries) - cold_complaints (sum of cold flags) - rate = cold_complaints / deliveries Then, per city, compute the median courier rate over all couriers in that city in this period. Finally, return couriers that: - Have deliveries >= 100 in this period, and - Have rate > 2 × city_median_rate. Use QUALIFY: - First, to pick the last 100 deliveries per courier. - Second, on the final aggregated result to filter out only the outlier couriers. Return: courier_id, city, deliveries, cold_complaints, rate, city_median_rate.

Tables

deliveries(delivery_id VARCHAR(20), order_id VARCHAR(20), courier_id VARCHAR(20), restaurant_id VARCHAR(20), city VARCHAR(50), pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), status VARCHAR(20))

complaints(complaint_id VARCHAR(20), order_id VARCHAR(20), complaint_ts TIMESTAMP, type VARCHAR(20))

order_events(order_id VARCHAR(20), event_ts TIMESTAMP, status VARCHAR(20))

couriers(courier_id VARCHAR(20), city VARCHAR(50), has_insulated_bag BOOLEAN, activation_date DATE)

Hints

  1. Use ROW_NUMBER() PARTITION BY courier_id ORDER BY dropoff_ts DESC and QUALIFY rn <= 100 to keep at most the last 100 deliveries per courier.
  2. Aggregate cold flags per courier to get their rate, compute the median rate per city with PERCENTILE_CONT, then QUALIFY couriers whose rate is more than double their city median and who have sufficient deliveries.

Top couriers per city by share of deliveries above P90 service time

You are analyzing courier service times to find couriers whose deliveries are slower than their city peers. All tables live in PostgreSQL. **Tables** - `order_events(order_id, event_ts, status)` — one row per lifecycle event of an order. Relevant statuses include `'picked_up'` and `'delivered'`. - `deliveries(delivery_id, order_id, courier_id, restaurant_id, city, pickup_ts, dropoff_ts, distance_km, status)` — one row per delivery; only rows with `status = 'completed'` are in scope. **Task** 1. For each `order_id`, compute `pickup_to_dropoff_min` = the number of minutes between the `'picked_up'` event and the immediately following `'delivered'` event. Use `LAG(event_ts) OVER (PARTITION BY order_id ORDER BY event_ts)` so that, at the `'delivered'` row, the previous event timestamp is the pickup; take the difference in minutes. 2. Join these per-order durations to `deliveries` (restricted to `status = 'completed'`) to attach each order's `city` and `courier_id`. 3. For each `city`, compute the 90th percentile (P90) of `pickup_to_dropoff_min` across its completed deliveries, using `PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY pickup_to_dropoff_min)`. Call it `p90_pickup_to_dropoff_min`. 4. For each (`courier_id`, `city`) pair, compute: - `deliveries` — number of completed deliveries by that courier in that city, - `deliveries_above_p90` — how many of those had `pickup_to_dropoff_min` **strictly greater** than the city's P90, - `share_above_p90` = `deliveries_above_p90 / deliveries`, rounded to 4 decimal places. 5. Within each city, rank couriers by `share_above_p90` **descending**, breaking ties by higher `deliveries` (and finally by `courier_id` ascending for full determinism), and keep only the **top 5 couriers per city**. (PostgreSQL has no `QUALIFY`, so apply the rank filter via a subquery / CTE plus a `WHERE rn <= 5`.) **Output** Return one row per qualifying courier-city with columns, in this order: `courier_id`, `city`, `deliveries`, `deliveries_above_p90`, `share_above_p90`, `p90_pickup_to_dropoff_min`. Sort the final result by `city` ascending, then `share_above_p90` descending, then `deliveries` descending, then `courier_id` ascending.

Tables

deliveries(delivery_id VARCHAR(20), order_id VARCHAR(20), courier_id VARCHAR(20), restaurant_id VARCHAR(20), city VARCHAR(50), pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), status VARCHAR(20))

complaints(complaint_id VARCHAR(20), order_id VARCHAR(20), complaint_ts TIMESTAMP, type VARCHAR(20))

order_events(order_id VARCHAR(20), event_ts TIMESTAMP, status VARCHAR(20))

couriers(courier_id VARCHAR(20), city VARCHAR(50), has_insulated_bag BOOLEAN, activation_date DATE)

Hints

  1. Postgres has no DATEDIFF: subtract the two timestamps to get an interval, then `EXTRACT(EPOCH FROM (delivered_ts - pickup_ts)) / 60.0` for minutes.
  2. Use `PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY pickup_to_dropoff_min)` grouped by city for the P90.

Loading coding console...