Analyze Driver Requests for Food Delivery Orders
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
ORDER_TABLE(order_id INTEGER, restaurant_id INTEGER, created_at TIMESTAMP, total_value DECIMAL(10,2))
REQUEST_TABLE(request_id INTEGER, order_id INTEGER, driver_id INTEGER, offer_amount DECIMAL(10,2), request_time TIMESTAMP)
Hints
- Join orders to requests on order_id before ranking.
- Use a window function partitioned by order_id and ordered by offer_amount descending.
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).
Tables
ORDER_TABLE(order_id INTEGER, restaurant_id INTEGER, created_at TIMESTAMP, total_value DECIMAL(10,2))
REQUEST_TABLE(request_id INTEGER, order_id INTEGER, driver_id INTEGER, offer_amount DECIMAL(10,2), request_time TIMESTAMP)
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.
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`.
Tables
ORDER_TABLE(order_id INTEGER, restaurant_id INTEGER, created_at TIMESTAMP, total_value DECIMAL(10,2))
REQUEST_TABLE(request_id INTEGER, order_id INTEGER, driver_id INTEGER, offer_amount DECIMAL(10,2), request_time TIMESTAMP)
Hints
- Partition both windows by order_id.
- Use ROWS BETWEEN 1 PRECEDING AND CURRENT ROW for the two-point moving average.