Quick Overview

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.

Compute courier pay with peak-hour rules

Company: DoorDash

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: 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.

Read the full DoorDash Software Engineer interview experience this question came from

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: 1. Each delivery's pay is based on base_rate, miles driven, and tip. 2. 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. 3. Compute the raw pay for each delivery as: raw_pay = cleaned_base_rate + cleaned_miles * 0.50 + cleaned_tip 4. Enforce a minimum pay per delivery: if raw_pay is less than 5.00, the delivery's pay should be 5.00. 5. 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)

Tables

deliveries(delivery_id INT, driver_id INT, delivery_date DATE, start_time TIME, miles DECIMAL(5,2), base_rate DECIMAL(6,2), tip DECIMAL(6,2))

Hints

  1. Clean and normalize invalid or NULL numeric fields using CASE and COALESCE-like logic before computing pay.
  2. 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: 1. First compute each delivery's base pay using the same cleaning, formula, and 5.00 minimum-per-delivery rules from Question 1. 2. 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. 3. 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. 4. The final pay per delivery is: final_pay_with_peak = base_delivery_pay * chosen_multiplier 5. 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)

Tables

deliveries(delivery_id INT, driver_id INT, delivery_date DATE, start_time TIME, miles DECIMAL(5,2), base_rate DECIMAL(6,2), tip DECIMAL(6,2))

peak_hour_rules(rule_id INT, start_time TIME, end_time TIME, multiplier DECIMAL(3,2))

Hints

  1. First build a CTE or subquery that reuses the cleaning and minimum-pay logic from the first question to compute per-delivery base pay.
  2. 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.

Loading coding console...