Coding questions · DoorDash

Compute dasher pay from deliveries

This question evaluates competency in data manipulation, schema design and validation, time-based aggregation, pay-computation logic, and handling of edge cases using SQL or Python.

Given a list of delivery events for dashers (e.g., dasherId, pickupTime, dropoffTime, distance, tip, and optional bonuses) and a set of pay rules (e.g., base pay per order, per-distance rate, per-minute rate, and additive tips/bonuses), implement a program that outputs each dasher’s total pay for a shift or day. Requirements: (1) Define and validate an input format and pay-rule schema; (2) Handle overlapping deliveries, missing fields, and rounding to cents; (3) Support grouping by day and by dasher, with output sorted by dasherId; (4) Provide tests covering at least three different pay configurations.

Compute dasher daily pay from deliveries and pay rules

You are given delivery events for food-delivery dashers and a set of pay rules. Each delivery record includes the dasher, pickup and dropoff timestamps, distance, tip, and an optional bonus. Each delivery row is associated with a pay configuration that defines: base pay per order, per-mile rate, and per-minute rate. Tips and bonuses are always additive.

Write a SQL query that outputs each dasher’s total pay per calendar day. Requirements:

Treat the work day as DATE(pickup_time).
Group the final output by day and dasher, and sort by dasher_id, then by date.
For per-minute pay, do not double-count time during overlapping deliveries. For each dasher and day, compute the total number of wall-clock minutes during which the dasher has at least one active delivery, then multiply that by the per-minute rate.
Use the pay configuration associated with each delivery to determine base, distance, and time pay. You may assume each dasher uses at most one pay_config_id per day.
Treat NULL in distance_miles, tip_amount, or bonus_amount as 0.
Round the final total pay to 2 decimal places (cents).

Your output should have one row per (dasher_id, work_date) with the dasher’s total daily pay.

Table `pay_configs`

column	type	constraints
pay_config_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
base_pay_per_order	DECIMAL(8,2)	NOT NULL
per_mile_rate	DECIMAL(8,2)	NOT NULL
per_minute_rate	DECIMAL(8,4)	NOT NULL

Sample data:

base_pay_per_order	name	pay_config_id	per_mile_rate	per_minute_rate
3	Standard	1	1	0.1
2.5	High mileage	2	1.5	0.05
4	No time pay	3	1	0

Table `deliveries`

column	type	constraints
delivery_id	INT	PRIMARY KEY
dasher_id	INT	NOT NULL
pay_config_id	INT	NOT NULL
pickup_time	TIMESTAMP	NOT NULL
dropoff_time	TIMESTAMP	NOT NULL
distance_miles	DECIMAL(8,2)
tip_amount	DECIMAL(8,2)
bonus_amount	DECIMAL(8,2)

Sample data:

bonus_amount	dasher_id	delivery_id	distance_miles	dropoff_time	pay_config_id	pickup_time	tip_amount
1	101	1	5	2025-05-31 10:30:00	1	2025-05-31 10:00:00	4
0	101	2	3	2025-05-31 10:45:00	1	2025-05-31 10:15:00	0
NULL	101	3	2	2025-05-31 11:10:00	1	2025-05-31 11:00:00	2.5
0	101	4	4	2025-06-01 18:20:00	2	2025-06-01 18:00:00	3
2	101	5	6	2025-06-01 18:40:00	2	2025-06-01 18:10:00	1
1	102	6	2.5	2025-05-31 09:20:00	1	2025-05-31 09:00:00	NULL
0	102	7	NULL	2025-05-31 10:00:00	1	2025-05-31 09:40:00	2
0	102	8	3	2025-06-01 12:15:00	3	2025-06-01 12:00:00	1
0	102	9	2	2025-06-01 12:25:00	3	2025-06-01 12:10:00	2

Expected output

Total pay per dasher per calendar work date (DATE(pickup_time)), including base pay per order, distance pay, non-overlapping per-minute pay, and tips/bonuses, rounded to 2 decimal places. Sorted by dasher_id, then work_date.

dasher_id	total_pay	work_date
101	32	2025-05-31
101	28	2025-06-01
102	15.5	2025-05-31
102	16	2025-06-01

Hints

First aggregate deliveries per dasher and day, but to handle per-minute pay correctly you must merge overlapping time intervals so you only count each active minute once.
Use window functions (LAG and a running SUM) to assign group ids to overlapping intervals, then aggregate by these groups to get merged start/end times before computing total active minutes.