Coding questions · DoorDash
Implement a gig worker payout calculator
This question evaluates skills in data modeling, time-series event handling, business-rule implementation, monetary rounding/precision, and idempotent processing, with an emphasis on SQL and Python proficiency for data manipulation.
Implement a payout calculator for gig workers (e.g., delivery drivers). Given a list of completed orders with timestamps, distances, and tips, plus policy tables for base pay, distance/time multipliers, surge/boosts, batching rules, cancellations, and minimum guarantees, compute per-order pay, per-shift summaries, and weekly statements. Handle edge cases such as partial cancellations, stacked deliveries, negative adjustments/chargebacks, rounding and currency precision, timezone boundaries, and idempotent reprocessing of late-arriving events. Provide function signatures or SQL, outline the schema, and include tests that cover typical and extreme scenarios.
Per-Order Gig Worker Payout Calculation
Using the tables orders, batches, base_pay_policy, surge_periods, and cancellation_policy, write a SQL query that computes the payout for each order for the sample week. For every row in orders, output one row with: order_id, worker_id, status, surge_multiplier, base_distance_cents_per_order, and payout_cents. Use the following rules: (1) Base and distance pay are determined per batch: batch_base_cents = base_per_order_cents + per_km_cents * batches.distance_km. For a batch with N orders, each order gets ROUND(batch_base_cents / N) cents of base+distance pay. (2) If the batch pickup_ts falls inside a surge_periods window for the same city_id, multiply the per-order base+distance pay by surge_multiplier; otherwise use 1.0. (3) For orders with status = 'completed', total payout_cents is the rounded surged base+distance pay plus tip_cents. (4) For any other status, ignore surge, distance, and tips and instead pay cancellation_policy.pay_cents. (5) All amounts are stored in cents as integers; when splitting batch pay and applying surge, round to the nearest cent. Return results for all rows in the orders table.
Table base_pay_policy
| column | type | constraints |
|---|
| effective_from | DATE | NOT NULL |
| effective_to | DATE | NOT NULL |
| base_per_order_cents | INT | NOT NULL |
| per_km_cents | INT | NOT NULL |
Sample data:
| base_per_order_cents | effective_from | effective_to | per_km_cents |
|---|
| 200 | 2025-01-01 | 2025-12-31 | 100 |
Table surge_periods
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| end_ts | TIMESTAMP | NOT NULL |
| surge_multiplier | DECIMAL(4,2) | NOT NULL |
Sample data:
| city_id | end_ts | start_ts | surge_multiplier |
|---|
| 1 | 2025-05-30 20:00:00 | 2025-05-30 18:00:00 | 1.5 |
| 1 | 2025-06-01 02:00:00 | 2025-05-31 22:00:00 | 2 |
Table cancellation_policy
| column | type | constraints |
|---|
| status | VARCHAR(32) | PRIMARY KEY |
| pay_cents | INT | NOT NULL |
Sample data:
| pay_cents | status |
|---|
| 0 | completed |
| 300 | cancelled_partial |
| 0 | cancelled_full |
Table batches
| column | type | constraints |
|---|
| batch_id | INT | PRIMARY KEY |
| worker_id | INT | NOT NULL |
| shift_id | INT | NOT NULL |
| pickup_ts | TIMESTAMP | NOT NULL |
| dropoff_ts | TIMESTAMP | NOT NULL |
| distance_km | DECIMAL(6,2) | NOT NULL |
| city_id | INT | NOT NULL |
Sample data:
| batch_id | city_id | distance_km | dropoff_ts | pickup_ts | shift_id | worker_id |
|---|
| 1 | 1 | 5 | 2025-05-29 10:30:00 | 2025-05-29 10:00:00 | 1 | 101 |
| 2 | 1 | 4 | 2025-05-30 18:45:00 | 2025-05-30 18:15:00 | 2 | 101 |
| 3 | 1 | 3 | 2025-06-01 00:10:00 | 2025-05-31 23:30:00 | 4 | 102 |
| 4 | 2 | 0 | 2025-05-27 12:00:00 | 2025-05-27 12:00:00 | 3 | 102 |
| 5 | 1 | 2 | 2025-05-31 09:00:00 | 2025-05-31 09:00:00 | 5 | 103 |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY |
| batch_id | INT | NOT NULL |
| worker_id | INT | NOT NULL |
| status | VARCHAR(32) | NOT NULL |
| tip_cents | INT | NOT NULL |
| completed_ts | TIMESTAMP | NOT NULL |
Sample data:
| batch_id | completed_ts | order_id | status | tip_cents | worker_id |
|---|
| 1 | 2025-05-29 10:30:00 | 1 | completed | 150 | 101 |
| 2 | 2025-05-30 18:40:00 | 2 | completed | 0 | 101 |
| 2 | 2025-05-30 18:42:00 | 3 | completed | 250 | 101 |
| 4 | 2025-05-27 12:05:00 | 4 | cancelled_partial | 0 | 102 |
| 5 | 2025-05-31 09:05:00 | 5 | cancelled_full | 0 | 103 |
| 3 | 2025-06-01 00:05:00 | 6 | completed | 100 | 102 |
Expected output
One row per order with the applied surge multiplier, the per-order base+distance pay before surge, and the final payout in cents after surge, tips, and cancellation rules.
| base_distance_cents_per_order | order_id | payout_cents | status | surge_multiplier | worker_id |
|---|
| 700 | 1 | 850 | completed | 1 | 101 |
| 300 | 2 | 450 | completed | 1.5 | 101 |
| 300 | 3 | 700 | completed | 1.5 | 101 |
| 200 | 4 | 300 | cancelled_partial | 1 | 102 |
| 400 | 5 | 0 | cancelled_full | 1 | 103 |
| 500 | 6 | 1100 | completed | 2 | 102 |
Hints
- First compute how many orders belong to each batch_id so you can split the batch base pay across orders.
- LEFT JOIN to surge_periods on city_id and pickup_ts, defaulting the surge multiplier to 1.0 when there is no matching surge window.
- Use CASE expressions to apply cancellation_policy.pay_cents for non-completed orders and to add surge and tips only for completed ones.
Per-Shift Gig Worker Earnings Summary
Using the same payout rules as in Question 1 and the additional shifts table, write a SQL query that produces a per-shift earnings summary for the sample data. Each shift is uniquely identified by shift_id. For every row in shifts, output: shift_id, worker_id, start_ts, end_ts, total_orders (count of orders in that shift, including cancelled ones), completed_orders (count of orders with status = 'completed'), and gross_earnings_cents (sum of per-order payout_cents including cancellation pay). Orders belong to a shift through their batch_id and the batches.shift_id column. Make sure the shift that crosses midnight is handled correctly by grouping by shift_id rather than by calendar date.
Table base_pay_policy
| column | type | constraints |
|---|
| effective_from | DATE | NOT NULL |
| effective_to | DATE | NOT NULL |
| base_per_order_cents | INT | NOT NULL |
| per_km_cents | INT | NOT NULL |
Sample data:
| base_per_order_cents | effective_from | effective_to | per_km_cents |
|---|
| 200 | 2025-01-01 | 2025-12-31 | 100 |
Table surge_periods
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| end_ts | TIMESTAMP | NOT NULL |
| surge_multiplier | DECIMAL(4,2) | NOT NULL |
Sample data:
| city_id | end_ts | start_ts | surge_multiplier |
|---|
| 1 | 2025-05-30 20:00:00 | 2025-05-30 18:00:00 | 1.5 |
| 1 | 2025-06-01 02:00:00 | 2025-05-31 22:00:00 | 2 |
Table cancellation_policy
| column | type | constraints |
|---|
| status | VARCHAR(32) | PRIMARY KEY |
| pay_cents | INT | NOT NULL |
Sample data:
| pay_cents | status |
|---|
| 0 | completed |
| 300 | cancelled_partial |
| 0 | cancelled_full |
Table batches
| column | type | constraints |
|---|
| batch_id | INT | PRIMARY KEY |
| worker_id | INT | NOT NULL |
| shift_id | INT | NOT NULL |
| pickup_ts | TIMESTAMP | NOT NULL |
| dropoff_ts | TIMESTAMP | NOT NULL |
| distance_km | DECIMAL(6,2) | NOT NULL |
| city_id | INT | NOT NULL |
Sample data:
| batch_id | city_id | distance_km | dropoff_ts | pickup_ts | shift_id | worker_id |
|---|
| 1 | 1 | 5 | 2025-05-29 10:30:00 | 2025-05-29 10:00:00 | 1 | 101 |
| 2 | 1 | 4 | 2025-05-30 18:45:00 | 2025-05-30 18:15:00 | 2 | 101 |
| 3 | 1 | 3 | 2025-06-01 00:10:00 | 2025-05-31 23:30:00 | 4 | 102 |
| 4 | 2 | 0 | 2025-05-27 12:00:00 | 2025-05-27 12:00:00 | 3 | 102 |
| 5 | 1 | 2 | 2025-05-31 09:00:00 | 2025-05-31 09:00:00 | 5 | 103 |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY |
| batch_id | INT | NOT NULL |
| worker_id | INT | NOT NULL |
| status | VARCHAR(32) | NOT NULL |
| tip_cents | INT | NOT NULL |
| completed_ts | TIMESTAMP | NOT NULL |
Sample data:
| batch_id | completed_ts | order_id | status | tip_cents | worker_id |
|---|
| 1 | 2025-05-29 10:30:00 | 1 | completed | 150 | 101 |
| 2 | 2025-05-30 18:40:00 | 2 | completed | 0 | 101 |
| 2 | 2025-05-30 18:42:00 | 3 | completed | 250 | 101 |
| 4 | 2025-05-27 12:05:00 | 4 | cancelled_partial | 0 | 102 |
| 5 | 2025-05-31 09:05:00 | 5 | cancelled_full | 0 | 103 |
| 3 | 2025-06-01 00:05:00 | 6 | completed | 100 | 102 |
Table shifts
| column | type | constraints |
|---|
| shift_id | INT | PRIMARY KEY |
| worker_id | INT | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| end_ts | TIMESTAMP | NOT NULL |
Sample data:
| end_ts | shift_id | start_ts | worker_id |
|---|
| 2025-05-29 12:00:00 | 1 | 2025-05-29 09:00:00 | 101 |
| 2025-05-30 20:00:00 | 2 | 2025-05-30 17:30:00 | 101 |
| 2025-05-27 13:00:00 | 3 | 2025-05-27 11:00:00 | 102 |
| 2025-06-01 00:30:00 | 4 | 2025-05-31 22:30:00 | 102 |
| 2025-05-31 10:00:00 | 5 | 2025-05-31 08:00:00 | 103 |
Expected output
One row per shift with counts of total and completed orders and the total gross earnings (including cancellation pay) for that shift.
| completed_orders | end_ts | gross_earnings_cents | shift_id | start_ts | total_orders | worker_id |
|---|
| 1 | 2025-05-29 12:00:00 | 850 | 1 | 2025-05-29 09:00:00 | 1 | 101 |
| 2 | 2025-05-30 20:00:00 | 1150 | 2 | 2025-05-30 17:30:00 | 2 | 101 |
| 0 | 2025-05-27 13:00:00 | 300 | 3 | 2025-05-27 11:00:00 | 1 | 102 |
| 1 | 2025-06-01 00:30:00 | 1100 | 4 | 2025-05-31 22:30:00 | 1 | 102 |
| 0 | 2025-05-31 10:00:00 | 0 | 5 | 2025-05-31 08:00:00 | 1 | 103 |
Hints
- Put the per-order payout logic into a CTE and then aggregate it by shift_id.
- Join shifts to batches and orders via shift_id so the shift that crosses midnight is handled as a single unit.
Weekly Gig Worker Statement with Minimum Guarantees and Adjustments
For the pay week starting 2025-05-26 and ending 2025-06-01 (inclusive), build a weekly statement per worker that applies minimum guarantees and adjustments on top of the per-order payouts defined in Question 1. Using orders, batches, base_pay_policy, surge_periods, cancellation_policy, adjustments, and weekly_minimum_guarantee, write a SQL query that returns one row per worker with: worker_id, week_start_date, order_earnings_cents (sum of payout_cents from all that worker's orders completed in the week), adjustment_cents (sum of adjustments.amount_cents whose effective_date falls in the week, which may be positive or negative), gross_earnings_cents (orders + adjustments), guaranteed_earnings_cents, guarantee_topup_cents (max(guaranteed_earnings_cents - gross_earnings_cents, 0)), and final_payout_cents (max(gross_earnings_cents, guaranteed_earnings_cents)). Use DATE/TIMESTAMP filters explicitly for the range 2025-05-26 through 2025-06-01, and do not rely on any precomputed aggregates so that the query is idempotent if re-run.
Table base_pay_policy
| column | type | constraints |
|---|
| effective_from | DATE | NOT NULL |
| effective_to | DATE | NOT NULL |
| base_per_order_cents | INT | NOT NULL |
| per_km_cents | INT | NOT NULL |
Sample data:
| base_per_order_cents | effective_from | effective_to | per_km_cents |
|---|
| 200 | 2025-01-01 | 2025-12-31 | 100 |
Table surge_periods
| column | type | constraints |
|---|
| city_id | INT | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| end_ts | TIMESTAMP | NOT NULL |
| surge_multiplier | DECIMAL(4,2) | NOT NULL |
Sample data:
| city_id | end_ts | start_ts | surge_multiplier |
|---|
| 1 | 2025-05-30 20:00:00 | 2025-05-30 18:00:00 | 1.5 |
| 1 | 2025-06-01 02:00:00 | 2025-05-31 22:00:00 | 2 |
Table cancellation_policy
| column | type | constraints |
|---|
| status | VARCHAR(32) | PRIMARY KEY |
| pay_cents | INT | NOT NULL |
Sample data:
| pay_cents | status |
|---|
| 0 | completed |
| 300 | cancelled_partial |
| 0 | cancelled_full |
Table batches
| column | type | constraints |
|---|
| batch_id | INT | PRIMARY KEY |
| worker_id | INT | NOT NULL |
| shift_id | INT | NOT NULL |
| pickup_ts | TIMESTAMP | NOT NULL |
| dropoff_ts | TIMESTAMP | NOT NULL |
| distance_km | DECIMAL(6,2) | NOT NULL |
| city_id | INT | NOT NULL |
Sample data:
| batch_id | city_id | distance_km | dropoff_ts | pickup_ts | shift_id | worker_id |
|---|
| 1 | 1 | 5 | 2025-05-29 10:30:00 | 2025-05-29 10:00:00 | 1 | 101 |
| 2 | 1 | 4 | 2025-05-30 18:45:00 | 2025-05-30 18:15:00 | 2 | 101 |
| 3 | 1 | 3 | 2025-06-01 00:10:00 | 2025-05-31 23:30:00 | 4 | 102 |
| 4 | 2 | 0 | 2025-05-27 12:00:00 | 2025-05-27 12:00:00 | 3 | 102 |
| 5 | 1 | 2 | 2025-05-31 09:00:00 | 2025-05-31 09:00:00 | 5 | 103 |
Table orders
| column | type | constraints |
|---|
| order_id | INT | PRIMARY KEY |
| batch_id | INT | NOT NULL |
| worker_id | INT | NOT NULL |
| status | VARCHAR(32) | NOT NULL |
| tip_cents | INT | NOT NULL |
| completed_ts | TIMESTAMP | NOT NULL |
Sample data:
| batch_id | completed_ts | order_id | status | tip_cents | worker_id |
|---|
| 1 | 2025-05-29 10:30:00 | 1 | completed | 150 | 101 |
| 2 | 2025-05-30 18:40:00 | 2 | completed | 0 | 101 |
| 2 | 2025-05-30 18:42:00 | 3 | completed | 250 | 101 |
| 4 | 2025-05-27 12:05:00 | 4 | cancelled_partial | 0 | 102 |
| 5 | 2025-05-31 09:05:00 | 5 | cancelled_full | 0 | 103 |
| 3 | 2025-06-01 00:05:00 | 6 | completed | 100 | 102 |
Table weekly_minimum_guarantee
| column | type | constraints |
|---|
| worker_id | INT | NOT NULL |
| week_start_date | DATE | NOT NULL |
| guaranteed_earnings_cents | INT | NOT NULL |
Sample data:
| guaranteed_earnings_cents | week_start_date | worker_id |
|---|
| 2000 | 2025-05-26 | 101 |
| 2500 | 2025-05-26 | 102 |
| 1500 | 2025-05-26 | 103 |
Table adjustments
| column | type | constraints |
|---|
| adjustment_id | INT | PRIMARY KEY |
| worker_id | INT | NOT NULL |
| effective_date | DATE | NOT NULL |
| amount_cents | INT | NOT NULL |
| reason | VARCHAR(100) | NOT NULL |
| order_id | INT | |
Sample data:
| adjustment_id | amount_cents | effective_date | order_id | reason | worker_id |
|---|
| 1 | 100 | 2025-05-31 | NULL | Promo bonus | 101 |
| 2 | -200 | 2025-05-31 | 2 | Fraud chargeback | 101 |
| 3 | 500 | 2025-06-01 | NULL | Weekly incentive | 102 |
Expected output
One row per worker for the week starting 2025-05-26, showing earnings from orders, adjustments, gross earnings, applied minimum guarantees, any top-up amount, and the final payout in cents.
| adjustment_cents | final_payout_cents | gross_earnings_cents | guarantee_topup_cents | guaranteed_earnings_cents | order_earnings_cents | week_start_date | worker_id |
|---|
| -100 | 2000 | 1900 | 100 | 2000 | 2000 | 2025-05-26 | 101 |
| 500 | 2500 | 1900 | 600 | 2500 | 1400 | 2025-05-26 | 102 |
| 0 | 1500 | 0 | 1500 | 1500 | 0 | 2025-05-26 | 103 |
Hints
- Reuse the per-order payout logic in a CTE and then aggregate payouts by worker over the fixed week 2025-05-26 to 2025-06-01.
- Aggregate adjustments over the same date range, join to weekly_minimum_guarantee, and use CASE or GREATEST-style logic to compute guarantee top-ups and final payouts.