Write SQL for deliveries analytics
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write SQL for the following analytics tasks. Assume a PostgreSQL-like dialect unless otherwise stated. Treat "today" as 2025-09-01.
Schema:
- users(user_id INT, signup_date DATE, city TEXT)
- orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP NULL, courier_id INT, distance_km NUMERIC(5,1), status TEXT CHECK (status IN ('created','in_progress','delivered','cancelled')), courier_type TEXT CHECK (courier_type IN ('biker','car')))
- couriers(courier_id INT, type TEXT CHECK (type IN ('biker','car')), start_date DATE, city TEXT)
- calendar_months(month_start DATE, month_label TEXT) -- month_start is the first day of the month
Sample data (minimal, for understanding only):
users
+---------+-------------+-----+
| user_id | signup_date | city|
+---------+-------------+-----+
| 1 | 2025-08-20 | SF |
| 2 | 2025-08-25 | SF |
| 3 | 2025-08-28 | NYC |
| 4 | 2025-07-15 | NYC |
| 5 | 2025-07-01 | SF |
+---------+-------------+-----+
orders
+----------+---------+---------------------+---------------------+------------+-------------+------------+--------------+
| order_id | user_id | created_at | delivered_at | courier_id | distance_km | status | courier_type |
+----------+---------+---------------------+---------------------+------------+-------------+------------+--------------+
| 101 | 1 | 2025-08-26 10:05:00 | 2025-08-26 10:42:00 | 9001 | 3.2 | delivered | biker |
| 102 | 1 | 2025-08-27 12:10:00 | NULL | 9002 | 4.5 | cancelled | car |
| 103 | 2 | 2025-08-30 18:00:00 | 2025-08-30 18:35:00 | 9003 | 2.0 | delivered | biker |
| 104 | 3 | 2025-08-31 09:15:00 | 2025-08-31 09:50:00 | 9004 | 5.1 | delivered | car |
| 105 | 3 | 2025-09-01 11:00:00 | NULL | 9004 | 1.2 | in_progress| biker |
| 106 | 4 | 2025-07-20 14:00:00 | 2025-07-20 14:50:00 | 9004 | 6.5 | delivered | car |
| 107 | 5 | 2025-07-01 08:30:00 | NULL | 9001 | 1.0 | cancelled | biker |
+----------+---------+---------------------+---------------------+------------+-------------+------------+--------------+
couriers
+------------+-------+------------+-----+
| courier_id | type | start_date | city|
+------------+-------+------------+-----+
| 9001 | biker | 2025-07-01 | SF |
| 9002 | car | 2025-06-10 | SF |
| 9003 | biker | 2025-08-15 | SF |
| 9004 | car | 2025-07-20 | NYC |
+------------+-------+------------+-----+
calendar_months
+-------------+------------+
| month_start | month_label|
+-------------+------------+
| 2025-07-01 | 2025-07 |
| 2025-08-01 | 2025-08 |
| 2025-09-01 | 2025-09 |
+-------------+------------+
Tasks:
A) Last 7 days conversion (2025-08-26 to 2025-09-01 inclusive): For each city and courier_type, compute conversion = delivered orders / created orders where created_at is in the window. Count an order as delivered if status = 'delivered' and delivered_at is not NULL (regardless of when delivered). Return city, courier_type, created_orders, delivered_orders, conversion.
B) Monthly volume with zero months: For the last 3 months (2025-07 to 2025-09), output month_label, courier_type, orders_created, orders_delivered, and GMV assuming GMV = COUNT(delivered orders) * 10. Include months with zero orders by left joining from calendar_months. Use created_at for month assignment.
C) Consecutive biker streaks: For each user, find the first occurrence of a streak of 3 consecutive delivered orders fulfilled by bikers (ignore non-delivered orders; any non-biker delivery breaks the streak). Output user_id, first_order_id, third_order_id, and minutes between their created_at timestamps. If no streak, exclude the user.
D) p95 delivery time: For orders created in the last 30 days (2025-08-03 to 2025-09-01) with status = 'delivered', compute the 95th percentile of delivery time in minutes by courier_type using an appropriate percentile function. Also return count per group.
E) Follow-up to B (city x month completeness): Break the monthly report down by city and courier_type so that every (city, month_label) combination present in users.city ∪ couriers.city appears, even if there were zero orders. Return zeros when no orders exist.
F) Aging undelivered: Find orders with created_at <= '2025-09-01 00:00:00' that were not delivered within 2 hours of creation (i.e., delivered_at is NULL or delivered_at > created_at + interval '2 hours'). Return order_id, user_id, courier_type, status, created_at, delivered_at, and a boolean is_stale_2h.
Provide performant SQL for each task, explaining any window functions or joins you rely on in comments.
Overview: This question evaluates SQL data manipulation and analytics skills—specifically aggregation, conditional counting, time-window filtering, joins, and handling of nulls and zero-count periods.
Read the full DoorDash Data Scientist interview experience this question came from
City and courier-type conversion over a fixed 7-day window
Using the schema below, write a PostgreSQL-style SQL query that, for the period from 2025-05-26 to 2025-06-01 inclusive, computes for each city (taken from users.city) and courier_type the number of created orders, the number of delivered orders, and the conversion rate. An order is considered in the window if its created_at timestamp is between 2025-05-26 00:00:00 and 2025-06-01 23:59:59. Count an order as delivered if status = 'delivered' and delivered_at IS NOT NULL (regardless of when it was delivered). Return columns: city, courier_type, created_orders, delivered_orders, conversion (delivered_orders / created_orders as a numeric value).
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- Join orders to users to obtain the city for each order.
- Use COUNT(*) with FILTER to separately count delivered orders and then compute the conversion ratio.
Monthly volume with zero months per courier type
For the last 3 calendar months (2025-04, 2025-05, and 2025-06), produce a monthly report by courier_type. A month is assigned to an order based on orders.created_at. For each (month_label, courier_type), output: month_label, courier_type, orders_created (number of orders created in that month), orders_delivered (orders with status = 'delivered' and delivered_at IS NOT NULL), and GMV defined as orders_delivered * 10. Use the calendar_months table so that months with zero orders for a given courier_type still appear with zeros.
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- Aggregate orders by DATE_TRUNC('month', created_at) and courier_type in a CTE.
- Cross join calendar_months with the set of courier types, then left join the aggregates and COALESCE nulls to zeros.
First streak of 3 consecutive delivered biker orders per user
For each user, consider only their delivered orders (status = 'delivered' and delivered_at IS NOT NULL), ordered by created_at. Within this ordered sequence, a streak is defined as 3 consecutive delivered orders all fulfilled by bikers (courier_type = 'biker'). Any delivered order with courier_type <> 'biker' breaks the streak. Non-delivered orders are ignored entirely. Write a SQL query that finds, for each user who has at least one such streak, the first occurrence of a streak of 3 consecutive delivered biker orders. Return columns: user_id, first_order_id (order_id of the first order in the streak), third_order_id (order_id of the third order in the streak), and minutes_between (the number of minutes between the created_at timestamps of the first and third orders). Exclude users with no streak.
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- First restrict to delivered orders and order them per user by created_at.
- Use LEAD window functions to inspect the next two delivered orders per user and identify streak starts, then rank and pick the earliest per user.
p95 delivery time by courier type over a 30-day window
For orders created between 2025-05-03 and 2025-06-01 inclusive, and with status = 'delivered' (and delivered_at IS NOT NULL), compute the 95th percentile (p95) of delivery time in minutes by courier_type. Delivery time is defined as delivered_at - created_at. Return one row per courier_type with columns: courier_type, order_count (number of delivered orders in the window), and p95_delivery_minutes.
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- First filter to delivered orders in the given date range and compute delivery time in minutes in a CTE.
- Use PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY delivery_minutes) grouped by courier_type, and also count(*) in the same grouping.
Monthly volume by city and courier type with complete city-month coverage
Extend the monthly report to be broken down by city and courier_type, and ensure completeness over city-month combinations. For the 3 months 2025-04, 2025-05, and 2025-06, and for every city that appears in users.city or couriers.city (i.e., the union of those city sets), output all combinations of (month_label, city, courier_type). For each such combination, compute orders_created (orders with created_at in that month for that city and courier_type), orders_delivered (status = 'delivered' and delivered_at IS NOT NULL), and GMV = orders_delivered * 10. Use created_at to assign orders to months and users.city to assign the city. Combinations with no orders should appear with zero counts and GMV.
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- Build a city dimension from the UNION of users.city and couriers.city, and a courier_type dimension from orders.
- Cross join months, cities, and courier types, then left join aggregated order stats and fill NULLs with zeros.
Identify orders that are stale for more than 2 hours
Find orders with created_at <= '2025-06-01 00:00:00' that were not delivered within 2 hours of creation. An order is considered stale (is_stale_2h = true) if delivered_at IS NULL or delivered_at > created_at + interval '2 hours'. Return columns: order_id, user_id, courier_type, status, created_at, delivered_at, and is_stale_2h (a boolean).
Render timestamp columns as `YYYY-MM-DD HH24:MI:SS`; `delivered_at` should remain NULL for undelivered stale orders.
Tables
users(user_id INT, signup_date DATE, city VARCHAR(50))
orders(order_id INT, user_id INT, created_at TIMESTAMP, delivered_at TIMESTAMP, courier_id INT, distance_km NUMERIC(5,1), status VARCHAR(20), courier_type VARCHAR(10))
couriers(courier_id INT, type VARCHAR(10), start_date DATE, city VARCHAR(50))
calendar_months(month_start DATE, month_label VARCHAR(7))
Hints
- Filter by created_at <= '2025-06-01 00:00:00' and then apply the staleness condition in the WHERE clause.
- Use a boolean expression like (delivered_at IS NULL OR delivered_at > created_at + interval '2 hours') and also project it as is_stale_2h.