Calculate Order Request Metrics

You are working with DoorDash order and delivery-request data. Write SQL to answer the questions below.

Tables:

1. orders

• order_id BIGINT, primary key
• customer_id BIGINT
• merchant_id BIGINT
• dasher_id BIGINT, nullable until assigned
• created_at_utc TIMESTAMP, when the order was placed
• delivered_at_utc TIMESTAMP, when the order was delivered; null if not delivered
• order_status VARCHAR, values include completed, cancelled, and refunded

2. delivery_requests

• request_id BIGINT, primary key
• order_id BIGINT, foreign key to orders.order_id
• dasher_id BIGINT, Dasher who received the offer
• requested_at_utc TIMESTAMP, when the offer/request was sent
• offer_amount_cents INTEGER, guaranteed offer amount shown to the Dasher
• request_status VARCHAR, values include accepted, declined, expired, and cancelled

Relationship and assumptions:

• One order can have zero or more delivery requests.
• Requests for the same order should be ordered by requested_at_utc, breaking ties by request_id.
• Analyze completed orders whose created_at_utc falls in a parameterized window [start_ts_utc, end_ts_utc).
• All timestamps are stored in UTC. If a business-day filter is needed, convert to the America/Los_Angeles timezone before filtering.
• Parameters: late_threshold_minutes and request_threshold.

Questions:

1. Calculate the percentage of completed orders that were late, where a late order is defined as an order whose delivery time is more than late_threshold_minutes after created_at_utc.

   • Required output columns: total_completed_orders, late_orders, pct_late_orders.

2. Calculate the percentage of completed orders that had at least request_threshold delivery requests. Also calculate the average number of delivery requests per completed order.

   • Include completed orders with zero requests in the denominator.
   • Required output columns: total_completed_orders, orders_with_at_least_threshold_requests, pct_orders_with_at_least_threshold_requests, avg_requests_per_order.

3. Calculate the percentage of completed orders that had at least one offer increase. An offer increase occurs when a request's offer_amount_cents is greater than the immediately previous request's offer_amount_cents for the same order.

   • Also calculate the average percentage offer increase across positive increase events, defined as (current_offer_amount_cents - previous_offer_amount_cents) / previous_offer_amount_cents.
   • Exclude transitions where the previous offer amount is null or zero from the percentage-increase calculation.
   • Required output columns: total_completed_orders, orders_with_offer_increase, pct_orders_with_offer_increase, avg_pct_offer_increase.

Return one query or a set of queries that produces these outputs.

Calculate Order Request Metrics

Table orders

column	type	constraints
order_id	BIGINT	PRIMARY KEY
customer_id	BIGINT
merchant_id	BIGINT
dasher_id	BIGINT
created_at_utc	TIMESTAMP
delivered_at_utc	TIMESTAMP
order_status	VARCHAR	CHECK (order_status IN ('completed','cancelled','refunded'))

Sample data:

created_at_utc	customer_id	dasher_id	delivered_at_utc	merchant_id	order_id	order_status
2023-01-01T10:00:00Z	1001	3001	2023-01-01T10:30:00Z	2001	1	completed
2023-01-01T11:15:00Z	1002	3002	2023-01-01T12:15:00Z	2001	2	completed
2023-01-01T13:00:00Z	1003	3003	2023-01-01T13:20:00Z	2002	3	completed
2023-01-01T15:30:00Z	1004	3004	2023-01-01T16:31:00Z	2003	4	completed
2023-01-01T17:00:00Z	1005	NULL	NULL	2004	5	cancelled
2022-12-31T23:50:00Z	1006	3005	2023-01-01T00:40:00Z	2002	6	completed

Table delivery_requests

column	type	constraints
request_id	BIGINT	PRIMARY KEY
order_id	BIGINT	FOREIGN KEY (order_id) REFERENCES orders(order_id)
dasher_id	BIGINT
requested_at_utc	TIMESTAMP
offer_amount_cents	INTEGER
request_status	VARCHAR	CHECK (request_status IN ('accepted','declined','expired','cancelled'))

Sample data:

dasher_id	offer_amount_cents	order_id	request_id	request_status	requested_at_utc
4001	500	1	10001	declined	2023-01-01T10:05:00Z
3001	500	1	10002	accepted	2023-01-01T10:10:00Z
4002	400	2	10003	declined	2023-01-01T11:16:00Z
4003	450	2	10004	declined	2023-01-01T11:25:00Z
3002	450	2	10005	accepted	2023-01-01T11:35:00Z
3003	600	3	10006	accepted	2023-01-01T13:05:00Z
3006	500	5	10007	cancelled	2023-01-01T17:05:00Z
3005	550	6	10008	accepted	2022-12-31T23:55:00Z

Expected output

Three-row, single result set where each row corresponds to one question (q1, q2, q3). Unrelated columns for a given question are null.

avg_pct_offer_increase	avg_requests_per_order	late_orders	metric	orders_with_at_least_threshold_requests	orders_with_offer_increase	pct_late_orders	pct_orders_with_at_least_threshold_requests	pct_orders_with_offer_increase	total_completed_orders
NULL	NULL	2	q1	NULL	NULL	0.5	NULL	NULL	4
NULL	1.5	NULL	q2	1	NULL	NULL	0.25	NULL	4
0.125	NULL	NULL	q3	NULL	1	NULL	NULL	0.25	4

Hints

1. Create a params CTE with literal values for start_ts_utc, end_ts_utc, late_threshold_minutes, and request_threshold (avoid colon-parameter syntax).
2. Filter completed orders in the window [start_ts_utc, end_ts_utc) and reuse this set across all questions.
3. Compute lateness in minutes via extract(epoch from delivered_at_utc - created_at_utc) / 60.0.
4. Count delivery requests per order with a LEFT JOIN to include orders with zero requests.
5. Detect offer increases using LAG over (requested_at_utc, request_id) for each order, then compare current vs previous offer_amount_cents.
6. Exclude percentage calculations where previous offer is null or zero; average only over positive increases.