Compute dasher pay from deliveries
Company: DoorDash
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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.
Tables
pay_configs(pay_config_id INT, name VARCHAR(50), base_pay_per_order DECIMAL(8,2), per_mile_rate DECIMAL(8,2), per_minute_rate DECIMAL(8,4))
deliveries(delivery_id INT, dasher_id INT, pay_config_id INT, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, distance_miles DECIMAL(8,2), tip_amount DECIMAL(8,2), bonus_amount DECIMAL(8,2))
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.