Late-Night Delivery Penalty

10pts
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)
15012024-08-01 00:30:00
25012024-08-01 01:15:00
35012024-08-01 02:45:00
45012024-08-01 04:20:00
55022024-08-01 01:00:00
65022024-08-01 02:00:00
75022024-08-01 03:00:00
85032024-08-01 22:00:00

Expected Output

dasher_id(INTEGER)delivery_date(DATE)total_bonus_pay(INTEGER)
5012024-08-0120

Tags

HardWindow FunctionsDate PartConditional Aggregation
30-40 min
34%

Hints