Coding questions · DoorDash
Compute dasher payout from API data
This question evaluates a candidate's proficiency in data manipulation and transaction aggregation using SQL or Python, encompassing time-zone-aware timestamp handling, pagination, late or missing events, cancellations and negative adjustments, rounding rules, and itemized breakdowns.
Given a REST endpoint GET /payout that returns each delivery’s components (base pay, distance/time bonuses, promotions, tips, fees, adjustments, taxes, refunds) with timestamps, compute each Dasher’s total pay for a pay period. Handle pagination, missing/late events, time zones, partial-day boundaries, cancellations/chargebacks, negative adjustments, and rounding rules. Provide a Python or SQL implementation that aggregates per Dasher and outputs totals and itemized breakdowns; include test cases and discuss complexity.
Compute Dasher Payout for a Pay Period with Component Breakdown
You are given payout events for food-delivery couriers (Dashers). Each payout event represents a single monetary component associated with a delivery or adjustment: base pay, bonuses, promotions, tips, fees, taxes, refunds, or chargebacks.
All payout events are stored in UTC. A bi-weekly pay period corresponding to 2025-05-20 to 2025-05-31 in America/Los_Angeles (UTC-7) is represented in UTC as:
- start of pay period (inclusive): 2025-05-20 07:00:00
- end of pay period (inclusive): 2025-06-01 06:59:59
Your task: using the tables below, write a SQL query that computes each Dasher’s payout for this pay period with an itemized breakdown by component type.
Requirements:
- Include only payout_events whose event_timestamp is between '2025-05-20 07:00:00' and '2025-06-01 06:59:59' (inclusive). This handles the time-zone and partial-day boundary logic.
- Events belong to the pay period when their event_timestamp falls in this window, even if the underlying delivery happened earlier or later (this models late tips or adjustments).
- Handle cancellations, chargebacks, and negative adjustments correctly by treating the amount as-is (it can be negative and should reduce pay).
- For each Dasher and component_type, compute the subtotal of amount and round it to 2 decimal places using standard rounding (ROUND(SUM(amount), 2)).
- For each Dasher, compute net_total as the sum of the rounded component subtotals (so the net_total matches the sum of the displayed breakdown numbers, even if this differs slightly from rounding the grand sum directly).
- Output one row per Dasher and component_type that occurs in the data, with columns:
- dasher_id
- dasher_name
- component_type
- component_total (rounded to 2 decimals)
- net_total (sum of that Dasher’s rounded component_totals)
Use only the tables and sample data described below. Do not assume any extra tables or columns.
Table dashers
| column | type | constraints |
|---|---|---|
| dasher_id | INT | PRIMARY KEY |
| dasher_name | VARCHAR(50) | NOT NULL |
Sample data:
| dasher_id | dasher_name |
|---|---|
| 1 | Alice Perez |
| 2 | Bob Smith |
Table payout_events
| column | type | constraints |
|---|---|---|
| payout_event_id | INT | PRIMARY KEY |
| dasher_id | INT | NOT NULL |
| delivery_id | INT | |
| component_type | VARCHAR(20) | NOT NULL |
| amount | DECIMAL(10,3) | NOT NULL |
| event_timestamp | TIMESTAMP | NOT NULL |
Sample data:
| amount | component_type | dasher_id | delivery_id | event_timestamp | payout_event_id |
|---|---|---|---|---|---|
| 5 | BASE_PAY | 1 | 101 | 2025-05-20 08:00:00 | 1 |
| 1.5 | DISTANCE_BONUS | 1 | 101 | 2025-05-20 08:00:00 | 2 |
| 3 | TIP | 1 | 101 | 2025-05-20 08:05:00 | 3 |
| 4.25 | BASE_PAY | 1 | 102 | 2025-05-31 06:00:00 | 4 |
| 2.5 | TIP | 1 | 102 | 2025-06-01 07:10:00 | 5 |
| 2 | PROMOTION | 1 | 103 | 2025-05-19 23:00:00 | 6 |
| 5 | BASE_PAY | 1 | 104 | 2025-05-30 09:00:00 | 7 |
| -5 | REFUND | 1 | 104 | 2025-05-30 10:00:00 | 8 |
| -1.25 | ADJUSTMENT | 1 | NULL | 2025-05-29 12:00:00 | 9 |
| 0.75 | TAX | 1 | NULL | 2025-05-21 12:00:00 | 10 |
| 6 | BASE_PAY | 2 | 201 | 2025-05-20 06:30:00 | 11 |
| 6 | BASE_PAY | 2 | 202 | 2025-05-20 07:30:00 | 12 |
| 4 | TIP | 2 | 202 | 2025-05-22 09:00:00 | 13 |
| 5.5 | BASE_PAY | 2 | 203 | 2025-05-31 23:30:00 | 14 |
| -4 | CHARGEBACK | 2 | 203 | 2025-06-01 05:00:00 | 15 |
| 1.005 | TIP | 2 | 204 | 2025-05-25 10:00:00 | 16 |
| 0.335 | ADJUSTMENT | 2 | NULL | 2025-05-26 14:00:00 | 17 |
| 0.99 | FEE | 2 | 205 | 2025-05-28 11:00:00 | 18 |
| 0.5 | TAX | 2 | NULL | 2025-05-29 16:00:00 | 19 |
Expected output
For the pay period from '2025-05-20 07:00:00' to '2025-06-01 06:59:59' UTC, return one row per Dasher and component_type present in that window, with the rounded subtotal per component and the Dasher's net_total (sum of rounded component subtotals). The sample output below is one valid ordering.
| component_total | component_type | dasher_id | dasher_name | net_total |
|---|---|---|---|---|
| -1.25 | ADJUSTMENT | 1 | Alice Perez | 13.25 |
| 14.25 | BASE_PAY | 1 | Alice Perez | 13.25 |
| 1.5 | DISTANCE_BONUS | 1 | Alice Perez | 13.25 |
| -5 | REFUND | 1 | Alice Perez | 13.25 |
| 0.75 | TAX | 1 | Alice Perez | 13.25 |
| 3 | TIP | 1 | Alice Perez | 13.25 |
| 0.34 | ADJUSTMENT | 2 | Bob Smith | 14.34 |
| 11.5 | BASE_PAY | 2 | Bob Smith | 14.34 |
| -4 | CHARGEBACK | 2 | Bob Smith | 14.34 |
| 0.99 | FEE | 2 | Bob Smith | 14.34 |
| 0.5 | TAX | 2 | Bob Smith | 14.34 |
| 5.01 | TIP | 2 | Bob Smith | 14.34 |
Hints
- Filter payout_events by the explicit UTC timestamp window for the pay period.
- First aggregate and ROUND per dasher and component_type, then sum these rounded subtotals per dasher to get net_total.