Quick Overview

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 gig worker payout calculator

Company: DoorDash

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

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

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

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.

Tables

base_pay_policy(effective_from DATE, effective_to DATE, base_per_order_cents INT, per_km_cents INT)

surge_periods(city_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, surge_multiplier DECIMAL(4,2))

cancellation_policy(status VARCHAR(32), pay_cents INT)

batches(batch_id INT, worker_id INT, shift_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), city_id INT)

orders(order_id INT, batch_id INT, worker_id INT, status VARCHAR(32), tip_cents INT, completed_ts TIMESTAMP)

Hints

  1. First compute how many orders belong to each batch_id so you can split the batch base pay across orders.
  2. 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.

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.

Tables

base_pay_policy(effective_from DATE, effective_to DATE, base_per_order_cents INT, per_km_cents INT)

surge_periods(city_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, surge_multiplier DECIMAL(4,2))

cancellation_policy(status VARCHAR(32), pay_cents INT)

batches(batch_id INT, worker_id INT, shift_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), city_id INT)

orders(order_id INT, batch_id INT, worker_id INT, status VARCHAR(32), tip_cents INT, completed_ts TIMESTAMP)

shifts(shift_id INT, worker_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP)

Hints

  1. Put the per-order payout logic into a CTE and then aggregate it by shift_id.
  2. 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.

Tables

base_pay_policy(effective_from DATE, effective_to DATE, base_per_order_cents INT, per_km_cents INT)

surge_periods(city_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, surge_multiplier DECIMAL(4,2))

cancellation_policy(status VARCHAR(32), pay_cents INT)

batches(batch_id INT, worker_id INT, shift_id INT, pickup_ts TIMESTAMP, dropoff_ts TIMESTAMP, distance_km DECIMAL(6,2), city_id INT)

orders(order_id INT, batch_id INT, worker_id INT, status VARCHAR(32), tip_cents INT, completed_ts TIMESTAMP)

weekly_minimum_guarantee(worker_id INT, week_start_date DATE, guaranteed_earnings_cents INT)

adjustments(adjustment_id INT, worker_id INT, effective_date DATE, amount_cents INT, reason VARCHAR(100), order_id INT)

Hints

  1. 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.
  2. 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.

Loading coding console...