Katkat
Back

Analyze Driver Requests for Food Delivery Orders

DoorDashData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.