Coding questions · DoorDash

Analyze Driver Requests for Food Delivery Orders

This question evaluates data manipulation and analytical SQL skills in the Data Manipulation (SQL/Python) domain, focusing on window functions for ranking and temporal comparison, joins, and aggregation when analyzing orders and driver requests.

ORDER_TABLE

order_id | restaurant_id | created_at | total_value

1 | 101 | 2024-06-01 12:01 | 45.50

2 | 102 | 2024-06-01 12:05 | 23.00

3 | 101 | 2024-06-01 12:10 | 30.00

REQUEST_TABLE

request_id | order_id | driver_id | offer_amount | request_time

1001 | 1 | 501 | 7.50 | 2024-06-01 12:01

1002 | 2 | 502 | 6.00 | 2024-06-01 12:05

1003 | 3 | 503 | 8.00 | 2024-06-01 12:10

Scenario

You have two tables—orders and driver requests—for a food-delivery marketplace.

Question

Return every order that has at least one driver request.

For each order, rank driver offers by amount and flag the highest.

Count orders where the latest offer is higher than the previous one and compute the average improvement.

Explain bounded vs. unbounded window frames, and show an example query using each.

Hints

JOIN the tables, use ROW_NUMBER() or RANK() OVER (PARTITION BY order_id ORDER BY offer_amount DESC), apply LAG for deltas, and illustrate ROWS BETWEEN clauses.

Rank Driver Offers per Order

Return one row per driver request for orders that have at least one request. Include order metadata, rank each request by offer amount descending within its order, and add a flag for whether the request is tied for the highest offer. Format created_at and request_time as YYYY-MM-DD HH24:MI:SS so the output is stable.

Table `ORDER_TABLE`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
restaurant_id	INTEGER	NOT NULL
created_at	TIMESTAMP	NOT NULL
total_value	DECIMAL(10,2)	NOT NULL

Sample data:

created_at	order_id	restaurant_id	total_value
2024-06-01 12:01	1	101	45.5
2024-06-01 12:05	2	102	23
2024-06-01 12:10	3	101	30

Table `REQUEST_TABLE`

column	type	constraints
request_id	INTEGER	PRIMARY KEY, NOT NULL
order_id	INTEGER	NOT NULL, REFERENCES ORDER_TABLE(order_id)
driver_id	INTEGER	NOT NULL
offer_amount	DECIMAL(10,2)	NOT NULL
request_time	TIMESTAMP	NOT NULL

Sample data:

driver_id	offer_amount	order_id	request_id	request_time
501	7.5	1	1001	2024-06-01 12:01
502	8	1	1004	2024-06-01 12:03
503	9	1	1005	2024-06-01 12:04
504	6	2	1002	2024-06-01 12:05
505	5.5	2	1006	2024-06-01 12:06
506	6.5	2	1007	2024-06-01 12:08
507	8	3	1003	2024-06-01 12:10

Expected output

One row per driver request for orders with at least one request, including order metadata, rank of offer_amount within the order (descending), and a flag for the highest offer (rank = 1).

created_at	driver_id	is_highest_offer	offer_amount	offer_rank	order_id	request_id	request_time	restaurant_id	total_value
2024-06-01 12:01:00	503	1	9.00	1	1	1005	2024-06-01 12:04:00	101	45.50
2024-06-01 12:01:00	502	0	8.00	2	1	1004	2024-06-01 12:03:00	101	45.50
2024-06-01 12:01:00	501	0	7.50	3	1	1001	2024-06-01 12:01:00	101	45.50
2024-06-01 12:05:00	506	1	6.50	1	2	1007	2024-06-01 12:08:00	102	23.00
2024-06-01 12:05:00	504	0	6.00	2	2	1002	2024-06-01 12:05:00	102	23.00
2024-06-01 12:05:00	505	0	5.50	3	2	1006	2024-06-01 12:06:00	102	23.00
2024-06-01 12:10:00	507	1	8.00	1	3	1003	2024-06-01 12:10:00	101	30.00

Hints

Join orders to requests on order_id before ranking.
Use a window function partitioned by order_id and ordered by offer_amount descending.
Format timestamp columns explicitly when the expected result requires a fixed string format.

Latest Offer Improvement Summary

For each order, compare the latest offer_amount (by request_time) to the immediately previous offer. Return one summary row with: raising_offer_orders_count (number of orders where latest > previous) and avg_improvement (average of the positive differences, ignoring orders without a previous offer).

Table `ORDER_TABLE`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
restaurant_id	INTEGER	NOT NULL
created_at	TIMESTAMP	NOT NULL
total_value	DECIMAL(10,2)	NOT NULL

Sample data:

created_at	order_id	restaurant_id	total_value
2024-06-01 12:01	1	101	45.5
2024-06-01 12:05	2	102	23
2024-06-01 12:10	3	101	30

Table `REQUEST_TABLE`

column	type	constraints
request_id	INTEGER	PRIMARY KEY, NOT NULL
order_id	INTEGER	NOT NULL, REFERENCES ORDER_TABLE(order_id)
driver_id	INTEGER	NOT NULL
offer_amount	DECIMAL(10,2)	NOT NULL
request_time	TIMESTAMP	NOT NULL

Sample data:

driver_id	offer_amount	order_id	request_id	request_time
501	7.5	1	1001	2024-06-01 12:01
502	8	1	1004	2024-06-01 12:03
503	9	1	1005	2024-06-01 12:04
504	6	2	1002	2024-06-01 12:05
505	5.5	2	1006	2024-06-01 12:06
506	6.5	2	1007	2024-06-01 12:08
507	8	3	1003	2024-06-01 12:10

Expected output

A single summary row. With the sample data, orders 1 and 2 have multiple offers and the latest offer is higher than the previous one for both; order 3 has only one offer and is ignored for the average. The count of raising orders is 2 and the average improvement is 1.0.

avg_improvement	raising_offer_orders_count
1	2

Hints

Use LAG(offer_amount) OVER (PARTITION BY order_id ORDER BY request_time) to get the immediately previous offer within each order.
Use ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY request_time DESC) to identify the latest request per order.
Aggregate only rows where improvement > 0; AVG will ignore NULL improvements automatically.

Bounded vs Unbounded Window Frames

For each driver request within an order, compute two windowed metrics over offer_amount.

Use REQUEST_TABLE. Within each order_id, order requests by request_time. Return these columns:

order_id
request_id
request_time: formatted as YYYY-MM-DD HH24:MI
offer_amount
moving_avg_offer_last2: average offer over the current row and one preceding row in the same order
running_max_offer: maximum offer from the first row in the order through the current row

Order by order_id, then request_time.

Table `ORDER_TABLE`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
restaurant_id	INTEGER	NOT NULL
created_at	TIMESTAMP	NOT NULL
total_value	DECIMAL(10,2)	NOT NULL

Sample data:

created_at	order_id	restaurant_id	total_value
2024-06-01 12:01	1	101	45.5
2024-06-01 12:05	2	102	23
2024-06-01 12:10	3	101	30

Table `REQUEST_TABLE`

column	type	constraints
request_id	INTEGER	PRIMARY KEY, NOT NULL
order_id	INTEGER	NOT NULL, REFERENCES ORDER_TABLE(order_id)
driver_id	INTEGER	NOT NULL
offer_amount	DECIMAL(10,2)	NOT NULL
request_time	TIMESTAMP	NOT NULL

Sample data:

driver_id	offer_amount	order_id	request_id	request_time
501	7.5	1	1001	2024-06-01 12:01
502	8	1	1004	2024-06-01 12:03
503	9	1	1005	2024-06-01 12:04
504	6	2	1002	2024-06-01 12:05
505	5.5	2	1006	2024-06-01 12:06
506	6.5	2	1007	2024-06-01 12:08
507	8	3	1003	2024-06-01 12:10

Expected output

One row per request with a bounded two-point moving average and an unbounded running maximum within each order.

moving_avg_offer_last2	offer_amount	order_id	request_id	request_time	running_max_offer
7.5	7.5	1	1001	2024-06-01 12:01	7.5
7.75	8	1	1004	2024-06-01 12:03	8
8.5	9	1	1005	2024-06-01 12:04	9
6	6	2	1002	2024-06-01 12:05	6
5.75	5.5	2	1006	2024-06-01 12:06	6
6	6.5	2	1007	2024-06-01 12:08	6.5
8	8	3	1003	2024-06-01 12:10	8

Hints

Partition both windows by order_id.
Use ROWS BETWEEN 1 PRECEDING AND CURRENT ROW for the two-point moving average.
Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for the running maximum.

Scenario

Question

Hints

Rank Driver Offers per Order

Table ORDER_TABLE

Table REQUEST_TABLE

Expected output

Hints

Latest Offer Improvement Summary

Table ORDER_TABLE

Table REQUEST_TABLE

Expected output

Hints

Bounded vs Unbounded Window Frames

Table ORDER_TABLE

Table REQUEST_TABLE

Expected output

Hints

Table `ORDER_TABLE`

Table `REQUEST_TABLE`

Table `ORDER_TABLE`

Table `REQUEST_TABLE`

Table `ORDER_TABLE`

Table `REQUEST_TABLE`