Coding questions · DoorDash
Compute courier pay with peak-hour rules
This question evaluates a candidate's data manipulation skills and competency in implementing business rules, including robust error handling, aggregation for per-day totals, minimum-pay enforcement, and time-window (peak-hour) pay adjustments.
Implement compute_pay(deliveries) to calculate a delivery driver's daily pay from a list of delivery records. Each record may include times, miles, base rate, and tip. Requirements: robust error handling for missing/invalid fields and sensible defaults or skipping; enforce minimum pay per delivery; and produce a per-day total. Follow-up: add a configurable 'peak hour' rule that increases pay for deliveries whose start times fall within specified time windows.
Compute daily courier pay with validation and minimum pay
You are given a deliveries table with one row per completed delivery. Write an SQL query to compute each driver's total pay per delivery_date with the following rules:
- Each delivery's pay is based on base_rate, miles driven, and tip.
- Handle missing or invalid numeric values robustly:
- If base_rate is NULL or negative, treat it as 3.00.
- If miles is NULL or negative, treat it as 0.
- If tip is NULL or negative, treat it as 0.
- Compute the raw pay for each delivery as: raw_pay = cleaned_base_rate + cleaned_miles * 0.50 + cleaned_tip
- Enforce a minimum pay per delivery: if raw_pay is less than 5.00, the delivery's pay should be 5.00.
- Return one row per (driver_id, delivery_date) with the sum of the final per-delivery pay as total_pay.
Output columns:
- driver_id
- delivery_date
- total_pay (sum of the final per-delivery pay for that day and driver)
Table deliveries
| column | type | constraints |
|---|---|---|
| delivery_id | INT | PRIMARY KEY |
| driver_id | INT | NOT NULL |
| delivery_date | DATE | NOT NULL |
| start_time | TIME | NOT NULL |
| miles | DECIMAL(5,2) | |
| base_rate | DECIMAL(6,2) | |
| tip | DECIMAL(6,2) |
Sample data:
| base_rate | delivery_date | delivery_id | driver_id | miles | start_time | tip |
|---|---|---|---|---|---|---|
| 4 | 2025-05-30 | 1 | 101 | 5 | 10:15:00 | 2 |
| NULL | 2025-05-30 | 2 | 101 | NULL | 12:00:00 | 3.5 |
| 2 | 2025-05-30 | 3 | 101 | 2 | 21:30:00 | NULL |
| 5 | 2025-05-31 | 4 | 101 | 10 | 18:45:00 | 0 |
| -1 | 2025-05-30 | 5 | 102 | 3 | 11:00:00 | 1 |
| 4 | 2025-05-31 | 6 | 102 | NULL | 22:15:00 | -2 |
Expected output
Total daily pay per driver after cleaning invalid/missing fields, computing per-delivery pay with the given formula, enforcing a 5.00 minimum per delivery, and summing by driver_id and delivery_date.
| delivery_date | driver_id | total_pay |
|---|---|---|
| 2025-05-30 | 101 | 20 |
| 2025-05-31 | 101 | 10 |
| 2025-05-30 | 102 | 5.5 |
| 2025-05-31 | 102 | 5 |
Hints
- Clean and normalize invalid or NULL numeric fields using CASE and COALESCE-like logic before computing pay.
- Compute the per-delivery final pay in a subquery, then aggregate by driver_id and delivery_date in the outer query.
Apply configurable peak-hour multipliers to courier pay
Extend the previous logic by adding configurable peak-hour pay rules. In addition to the deliveries table, you now have a peak_hour_rules table that defines time windows and multipliers.
Tables:
- deliveries: same as in Question 1.
- peak_hour_rules: each row defines a time window and a pay multiplier.
peak_hour_rules columns:
- rule_id (primary key)
- start_time (TIME): inclusive window start
- end_time (TIME): inclusive window end
- multiplier (DECIMAL): factor by which the per-delivery pay is multiplied
Requirements:
- First compute each delivery's base pay using the same cleaning, formula, and 5.00 minimum-per-delivery rules from Question 1.
- A delivery is considered a peak-hour delivery if its start_time falls between start_time and end_time (inclusive) of any peak_hour_rules row.
- If a delivery matches one or more rules, apply the highest multiplier for that delivery. If it matches no rules, use a multiplier of 1.0.
- The final pay per delivery is: final_pay_with_peak = base_delivery_pay * chosen_multiplier
- Return one row per (driver_id, delivery_date) with the sum of final_pay_with_peak as total_pay.
Output columns:
- driver_id
- delivery_date
- total_pay (after applying peak-hour multipliers)
Table deliveries
| column | type | constraints |
|---|---|---|
| delivery_id | INT | PRIMARY KEY |
| driver_id | INT | NOT NULL |
| delivery_date | DATE | NOT NULL |
| start_time | TIME | NOT NULL |
| miles | DECIMAL(5,2) | |
| base_rate | DECIMAL(6,2) | |
| tip | DECIMAL(6,2) |
Sample data:
| base_rate | delivery_date | delivery_id | driver_id | miles | start_time | tip |
|---|---|---|---|---|---|---|
| 4 | 2025-05-30 | 1 | 101 | 5 | 10:15:00 | 2 |
| NULL | 2025-05-30 | 2 | 101 | NULL | 12:00:00 | 3.5 |
| 2 | 2025-05-30 | 3 | 101 | 2 | 21:30:00 | NULL |
| 5 | 2025-05-31 | 4 | 101 | 10 | 18:45:00 | 0 |
| -1 | 2025-05-30 | 5 | 102 | 3 | 11:00:00 | 1 |
| 4 | 2025-05-31 | 6 | 102 | NULL | 22:15:00 | -2 |
Table peak_hour_rules
| column | type | constraints |
|---|---|---|
| rule_id | INT | PRIMARY KEY |
| start_time | TIME | NOT NULL |
| end_time | TIME | NOT NULL |
| multiplier | DECIMAL(3,2) | NOT NULL |
Sample data:
| end_time | multiplier | rule_id | start_time |
|---|---|---|---|
| 14:00:00 | 1.5 | 1 | 11:00:00 |
| 21:00:00 | 1.2 | 2 | 18:00:00 |
Expected output
Total daily pay per driver after applying the same cleaning/minimum rules as Question 1, then multiplying per-delivery pay by the appropriate peak-hour multiplier (1.5 for 11:00–14:00, 1.2 for 18:00–21:00, or 1.0 otherwise) and summing by driver_id and delivery_date.
| delivery_date | driver_id | total_pay |
|---|---|---|
| 2025-05-30 | 101 | 23.25 |
| 2025-05-31 | 101 | 12 |
| 2025-05-30 | 102 | 8.25 |
| 2025-05-31 | 102 | 5 |
Hints
- First build a CTE or subquery that reuses the cleaning and minimum-pay logic from the first question to compute per-delivery base pay.
- Join deliveries to peak_hour_rules on the start_time falling within the rule window, aggregate to get the maximum multiplier per delivery, then multiply and sum per day.