Coding questions · DoorDash

Write complex SQL on DoorDash data

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.

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.

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):

Higher GMV first
If tied on GMV, fewer cancellations first
If still tied, lower merchant_id first

Output columns:

city
merchant_id
rank_in_city (1..3)
gmv
cancellations

Table `customers`

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
city	VARCHAR(50)	NOT NULL

Sample data:

city	customer_id	signup_date
SF	1	2025-04-10
SF	2	2025-05-01
NYC	3	2025-05-20
NYC	4	2025-03-15
SF	5	2025-05-01

Table `merchants`

column	type	constraints
merchant_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
city	VARCHAR(50)	NOT NULL
cuisine	VARCHAR(50)	NOT NULL

Sample data:

city	cuisine	merchant_id	name
SF	Japanese	10	SushiCo
SF	Mexican	11	TacoTown
SF	American	14	BurgerBay
NYC	Italian	12	PizzaBox
NYC	American	13	BagelHub
NYC	Japanese	15	RamenNY

Table `dashers`

column	type	constraints
dasher_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
vehicle_type	VARCHAR(20)	NOT NULL

Sample data:

dasher_id	signup_date	vehicle_type
100	2025-01-01	car
101	2025-05-15	bike
102	2025-05-20	car

Table `promotions`

column	type	constraints
promo_id	INT	PRIMARY KEY, NOT NULL
promo_type	VARCHAR(50)	NOT NULL
discount_amount	DECIMAL(10,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

discount_amount	end_date	promo_id	promo_type	start_date
10	2025-05-31	900	pct10	2025-05-01
5	2025-06-10	901	flat5	2025-05-15

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
merchant_id	INT	NOT NULL
dasher_id	INT
created_at	TIMESTAMP	NOT NULL
accepted_at	TIMESTAMP
picked_up_at	TIMESTAMP
delivered_at	TIMESTAMP
canceled_at	TIMESTAMP
subtotal	DECIMAL(10,2)	NOT NULL
delivery_fee	DECIMAL(10,2)	NOT NULL
tip_amount	DECIMAL(10,2)	NOT NULL
promo_id	INT
status	VARCHAR(20)	NOT NULL

Sample data:

accepted_at	canceled_at	created_at	customer_id	dasher_id	delivered_at	delivery_fee	merchant_id	order_id	picked_up_at	promo_id	status	subtotal	tip_amount
2025-05-05 12:02:00	NULL	2025-05-05 12:00:00	2	100	2025-05-05 12:35:00	3.99	14	6007	2025-05-05 12:15:00	NULL	delivered	25	4
2025-05-10 13:05:00	NULL	2025-05-10 13:00:00	1	100	2025-05-10 13:30:00	3.99	10	6008	2025-05-10 13:20:00	900	delivered	40	6
2025-05-12 11:01:00	NULL	2025-05-12 11:00:00	3	100	2025-05-12 11:30:00	4.99	12	6010	2025-05-12 11:10:00	NULL	delivered	18	2
NULL	2025-05-15 18:05:00	2025-05-15 18:00:00	1	100	NULL	2.99	10	6009	NULL	NULL	canceled	20	0
2025-05-18 09:02:00	NULL	2025-05-18 09:00:00	4	101	2025-05-18 09:40:00	2.99	13	6011	2025-05-18 09:10:00	901	delivered	12	1
2025-05-22 19:02:00	NULL	2025-05-22 19:00:00	3	102	2025-05-22 19:35:00	3.99	15	6012	2025-05-22 19:15:00	NULL	delivered	30	5
NULL	2025-05-23 20:05:00	2025-05-23 20:00:00	3	102	NULL	3.99	15	6016	NULL	NULL	canceled	16	0
2025-05-26 12:02:00	NULL	2025-05-26 12:00:00	1	100	2025-05-26 12:40:00	3.99	10	6001	2025-05-26 12:20:00	900	delivered	30	5
2025-05-27 18:08:00	NULL	2025-05-27 18:05:00	2	NULL	2025-05-27 19:10:00	2.99	11	6002	2025-05-27 18:30:00	NULL	delivered	15	0
NULL	2025-05-28 10:05:00	2025-05-28 10:00:00	5	101	NULL	2.99	10	6014	NULL	NULL	canceled	12	0
2025-05-29 19:05:00	NULL	2025-05-29 19:00:00	1	101	NULL	2.99	11	6003	2025-05-29 19:25:00	901	in_progress	20	3
NULL	2025-05-29 10:07:00	2025-05-29 10:00:00	5	101	NULL	3.99	14	6015	NULL	NULL	canceled	14	0
2025-05-30 11:31:00	NULL	2025-05-30 11:30:00	3	100	2025-05-30 12:05:00	4.99	12	6004	NULL	NULL	delivered	22	0
2025-05-31 20:01:00	NULL	2025-05-31 20:00:00	3	102	2025-05-31 20:50:00	4.99	12	6005	2025-05-31 20:10:00	NULL	delivered	28	2
NULL	2025-05-25 20:10:00	2025-05-25 20:00:00	2	NULL	NULL	2.99	11	6013	NULL	NULL	canceled	15	0
NULL	2025-06-01 09:02:00	2025-06-01 09:00:00	4	101	NULL	3.99	13	6006	NULL	NULL	canceled	18	0

Table `order_events`

column	type	constraints
order_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR(30)	NOT NULL

Sample data:

event_time	event_type	order_id
2025-05-26 12:00:00	created	6001

Expected output

Top 3 merchants per city (in May 2025) by GMV, with the specified tie-breakers. GMV excludes canceled orders; cancellations counts canceled orders in May.

cancellations	city	gmv	merchant_id	rank_in_city
0	NYC	86.97	12	1
1	NYC	38.99	15	2
0	NYC	10.99	13	3
2	SF	68.98	10	1
1	SF	38.98	11	2
1	SF	32.99	14	3

Hints

Compute GMV at the order level first (including discount) and then aggregate to merchant.
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.

Table `customers`

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
city	VARCHAR(50)	NOT NULL

Sample data:

city	customer_id	signup_date
SF	1	2025-04-10

Table `merchants`

column	type	constraints
merchant_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
city	VARCHAR(50)	NOT NULL
cuisine	VARCHAR(50)	NOT NULL

Sample data:

city	cuisine	merchant_id	name
NYC	Italian	12	PizzaBox

Table `dashers`

column	type	constraints
dasher_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
vehicle_type	VARCHAR(20)	NOT NULL

Sample data:

dasher_id	signup_date	vehicle_type
100	2025-01-01	car

Table `promotions`

column	type	constraints
promo_id	INT	PRIMARY KEY, NOT NULL
promo_type	VARCHAR(50)	NOT NULL
discount_amount	DECIMAL(10,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

discount_amount	end_date	promo_id	promo_type	start_date
10	2025-05-31	900	pct10	2025-05-01

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
merchant_id	INT	NOT NULL
dasher_id	INT
created_at	TIMESTAMP	NOT NULL
accepted_at	TIMESTAMP
picked_up_at	TIMESTAMP
delivered_at	TIMESTAMP
canceled_at	TIMESTAMP
subtotal	DECIMAL(10,2)	NOT NULL
delivery_fee	DECIMAL(10,2)	NOT NULL
tip_amount	DECIMAL(10,2)	NOT NULL
promo_id	INT
status	VARCHAR(20)	NOT NULL

Sample data:

accepted_at	canceled_at	created_at	customer_id	dasher_id	delivered_at	delivery_fee	merchant_id	order_id	picked_up_at	promo_id	status	subtotal	tip_amount
2025-05-30 11:31:00	NULL	2025-05-30 11:30:00	3	100	2025-05-30 12:05:00	4.99	12	6004	NULL	NULL	delivered	22	0

Table `order_events`

column	type	constraints
order_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR(30)	NOT NULL

Sample data:

event_time	event_type	order_id
2025-05-30 11:30:00	created	6004
2025-05-30 11:31:00	accepted	6004
2025-05-30 12:05:00	delivered	6004
2025-05-26 12:00:00	created	6001
2025-05-26 12:20:00	picked_up	6001
2025-05-26 12:40:00	delivered	6001

Expected output

Orders where a delivered event exists but there is no picked_up event strictly earlier than delivered_time (picked_up missing or picked_up at/after delivered_time).

created_time	delivered_time	order_id	picked_up_time
2025-05-30 11:30:00	2025-05-30 12:05:00	6004	NULL

Hints

Aggregate event timestamps per order with conditional MIN.
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.

Table `customers`

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
city	VARCHAR(50)	NOT NULL

Sample data:

city	customer_id	signup_date
SF	1	2025-04-10
SF	5	2025-05-01

Table `merchants`

column	type	constraints
merchant_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
city	VARCHAR(50)	NOT NULL
cuisine	VARCHAR(50)	NOT NULL

Sample data:

city	cuisine	merchant_id	name
SF	Japanese	10	SushiCo
SF	American	14	BurgerBay

Table `dashers`

column	type	constraints
dasher_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
vehicle_type	VARCHAR(20)	NOT NULL

Sample data:

dasher_id	signup_date	vehicle_type
101	2025-05-15	bike

Table `promotions`

column	type	constraints
promo_id	INT	PRIMARY KEY, NOT NULL
promo_type	VARCHAR(50)	NOT NULL
discount_amount	DECIMAL(10,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

discount_amount	end_date	promo_id	promo_type	start_date
5	2025-06-10	901	flat5	2025-05-15

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
merchant_id	INT	NOT NULL
dasher_id	INT
created_at	TIMESTAMP	NOT NULL
accepted_at	TIMESTAMP
picked_up_at	TIMESTAMP
delivered_at	TIMESTAMP
canceled_at	TIMESTAMP
subtotal	DECIMAL(10,2)	NOT NULL
delivery_fee	DECIMAL(10,2)	NOT NULL
tip_amount	DECIMAL(10,2)	NOT NULL
promo_id	INT
status	VARCHAR(20)	NOT NULL

Sample data:

accepted_at	canceled_at	created_at	customer_id	dasher_id	delivered_at	delivery_fee	merchant_id	order_id	picked_up_at	promo_id	status	subtotal	tip_amount
NULL	2025-05-28 10:05:00	2025-05-28 10:00:00	5	101	NULL	2.99	10	6014	NULL	NULL	canceled	12	0
NULL	2025-05-29 10:07:00	2025-05-29 10:00:00	5	101	NULL	3.99	14	6015	NULL	NULL	canceled	14	0
2025-05-26 12:02:00	NULL	2025-05-26 12:00:00	1	100	2025-05-26 12:40:00	3.99	10	6001	2025-05-26 12:20:00	900	delivered	30	5

Table `order_events`

column	type	constraints
order_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR(30)	NOT NULL

Sample data:

event_time	event_type	order_id
2025-05-28 10:00:00	created	6014

Expected output

Customers who have exactly two orders total and both have status='canceled', along with their cancellation timestamps ordered ascending.

canceled_at_1	canceled_at_2	customer_id
2025-05-28 10:05:00	2025-05-29 10:07:00	5

Hints

Group by customer_id and keep only customers with COUNT(*) = 2 and two canceled orders.
Use ARRAY_AGG(canceled_at ORDER BY canceled_at) to access the earlier and later cancellation timestamps.
Use TO_CHAR rather than strftime in PostgreSQL.

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.

Table `customers`

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
city	VARCHAR(50)	NOT NULL

Sample data:

city	customer_id	signup_date
SF	1	2025-04-10
SF	2	2025-05-01
NYC	3	2025-05-20
NYC	4	2025-03-15

Table `merchants`

column	type	constraints
merchant_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
city	VARCHAR(50)	NOT NULL
cuisine	VARCHAR(50)	NOT NULL

Sample data:

city	cuisine	merchant_id	name
SF	Japanese	10	SushiCo
SF	Mexican	11	TacoTown
NYC	Italian	12	PizzaBox
NYC	American	13	BagelHub
SF	American	14	BurgerBay
NYC	Japanese	15	RamenNY

Table `dashers`

column	type	constraints
dasher_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
vehicle_type	VARCHAR(20)	NOT NULL

Sample data:

dasher_id	signup_date	vehicle_type
100	2025-01-01	car
101	2025-05-15	bike
102	2025-05-20	car

Table `promotions`

column	type	constraints
promo_id	INT	PRIMARY KEY, NOT NULL
promo_type	VARCHAR(50)	NOT NULL
discount_amount	DECIMAL(10,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

discount_amount	end_date	promo_id	promo_type	start_date
10	2025-05-31	900	pct10	2025-05-01
5	2025-06-10	901	flat5	2025-05-15

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
merchant_id	INT	NOT NULL
dasher_id	INT
created_at	TIMESTAMP	NOT NULL
accepted_at	TIMESTAMP
picked_up_at	TIMESTAMP
delivered_at	TIMESTAMP
canceled_at	TIMESTAMP
subtotal	DECIMAL(10,2)	NOT NULL
delivery_fee	DECIMAL(10,2)	NOT NULL
tip_amount	DECIMAL(10,2)	NOT NULL
promo_id	INT
status	VARCHAR(20)	NOT NULL

Sample data:

accepted_at	canceled_at	created_at	customer_id	dasher_id	delivered_at	delivery_fee	merchant_id	order_id	picked_up_at	promo_id	status	subtotal	tip_amount
2025-05-05 12:02:00	NULL	2025-05-05 12:00:00	2	100	2025-05-05 12:35:00	3.99	14	6007	2025-05-05 12:15:00	NULL	delivered	25	4
2025-05-10 13:05:00	NULL	2025-05-10 13:00:00	1	100	2025-05-10 13:30:00	3.99	10	6008	2025-05-10 13:20:00	900	delivered	40	6
2025-05-12 11:01:00	NULL	2025-05-12 11:00:00	3	100	2025-05-12 11:30:00	4.99	12	6010	2025-05-12 11:10:00	NULL	delivered	18	2
2025-05-18 09:02:00	NULL	2025-05-18 09:00:00	4	101	2025-05-18 09:40:00	2.99	13	6011	2025-05-18 09:10:00	901	delivered	12	1
2025-05-22 19:02:00	NULL	2025-05-22 19:00:00	3	102	2025-05-22 19:35:00	3.99	15	6012	2025-05-22 19:15:00	NULL	delivered	30	5
2025-05-26 12:02:00	NULL	2025-05-26 12:00:00	1	100	2025-05-26 12:40:00	3.99	10	6001	2025-05-26 12:20:00	900	delivered	30	5
2025-05-27 18:08:00	NULL	2025-05-27 18:05:00	2	NULL	2025-05-27 19:10:00	2.99	11	6002	2025-05-27 18:30:00	NULL	delivered	15	0
2025-05-29 19:05:00	NULL	2025-05-29 19:00:00	1	101	NULL	2.99	11	6003	2025-05-29 19:25:00	901	in_progress	20	3
2025-05-30 11:31:00	NULL	2025-05-30 11:30:00	3	100	2025-05-30 12:05:00	4.99	12	6004	NULL	NULL	delivered	22	0
2025-05-31 20:01:00	NULL	2025-05-31 20:00:00	3	102	2025-05-31 20:50:00	4.99	12	6005	2025-05-31 20:10:00	NULL	delivered	28	2

Table `order_events`

column	type	constraints
order_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
event_type	VARCHAR(30)	NOT NULL

Sample data:

event_time	event_type	order_id
2025-05-26 12:00:00	created	6001

Expected output

One row per merchant with counts and AOV split by whether promo_id is present, plus the difference (promo - non-promo).

aov_nonpromo	aov_promo	diff	merchant_id	non_promo_orders	promo_orders
NULL	34.49	NULL	10	0	2
17.99	20.99	3	11	1	1
28.99	NULL	NULL	12	3	0
NULL	10.99	NULL	13	0	1
32.99	NULL	NULL	14	1	0
38.99	NULL	NULL	15	1	0

Hints

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

Top merchants per city by GMV with tie-breakers

Table customers

Table merchants

Table dashers

Table promotions

Table orders

Table order_events

Expected output

Hints

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

Table customers

Table merchants

Table dashers

Table promotions

Table orders

Table order_events

Expected output

Hints

Customers with exactly two lifetime orders and both canceled

Table customers

Table merchants

Table dashers

Table promotions

Table orders

Table order_events

Expected output

Hints

Promo vs non-promo AOV uplift per merchant

Table customers

Table merchants

Table dashers

Table promotions

Table orders

Table order_events

Expected output

Hints

Table `customers`

Table `merchants`

Table `dashers`

Table `promotions`

Table `orders`

Table `order_events`

Table `customers`

Table `merchants`

Table `dashers`

Table `promotions`

Table `orders`

Table `order_events`

Table `customers`

Table `merchants`

Table `dashers`

Table `promotions`

Table `orders`

Table `order_events`

Table `customers`

Table `merchants`

Table `dashers`

Table `promotions`

Table `orders`

Table `order_events`