Late-Night Delivery Penalty
Door Dash
Problem Statement
To ensure high availability during off-peak hours, DoorDash provides an incentive for Dashers working the graveyard shift.
A Dasher is eligible for a "Late-Night Bonus" if they complete more than 3 deliveries in a single night between 12:00 AM (00:00) and 5:00 AM (05:00). For every delivery completed during this window on a qualifying night, the Dasher receives an extra $5.00.
Write a query to calculate the total bonus pay for each Dasher per night.
Rules:
- A "night" is defined by the date the shift began (e.g., deliveries at 1 AM on Jan 2nd belong to the Jan 2nd session).
- Only deliveries where delivery_timestamp is >= 00:00:00 and < 05:00:00 count toward the total and the bonus.
- A Dasher must have > 3 deliveries in this window to qualify.
- Output columns: dasher_id, delivery_date, total_bonus_pay.
- Result must be sorted by delivery_date descending, then dasher_id ascending.
Tests your understanding of
Window Functions, Date Part and Conditional Aggregation
Input Tables
deliveries
| delivery_id(INTEGER) | dasher_id(INTEGER) | delivery_timestamp(TIMESTAMP) |
|---|---|---|
| 1 | 501 | 2024-08-01 00:30:00 |
| 2 | 501 | 2024-08-01 01:15:00 |
| 3 | 501 | 2024-08-01 02:45:00 |
| 4 | 501 | 2024-08-01 04:20:00 |
| 5 | 502 | 2024-08-01 01:00:00 |
| 6 | 502 | 2024-08-01 02:00:00 |
| 7 | 502 | 2024-08-01 03:00:00 |
| 8 | 503 | 2024-08-01 22:00:00 |
Expected Output
| dasher_id(INTEGER) | delivery_date(DATE) | total_bonus_pay(INTEGER) |
|---|---|---|
| 501 | 2024-08-01 | 20 |
Tags
HardWindow FunctionsDate PartConditional Aggregation
30-40 min
34%