Write SQL for percent and window changes
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Use PostgreSQL. Assume today = 2025-09-01. You must use CTEs and multiple window functions. Schema and tiny samples are below.
Schema:
- exposures(unit_id INT, dt DATE, condition_label INT) -- 1=treatment, 0=control
- orders(order_id INT, unit_id INT, dt DATE, courier_type TEXT, temp_category TEXT, subtotal_cents INT)
Sample rows:
exposures
+---------+------------+----------------+
| unit_id | dt | condition_label|
+---------+------------+----------------+
| 101 | 2025-08-30 | 1 |
| 101 | 2025-08-31 | 1 |
| 101 | 2025-09-01 | 0 |
| 102 | 2025-08-30 | 0 |
| 102 | 2025-08-31 | 1 |
| 102 | 2025-09-01 | 1 |
| 103 | 2025-08-31 | 0 |
| 103 | 2025-09-01 | 0 |
+---------+------------+----------------+
orders
+----------+---------+------------+--------------+---------------+----------------+
| order_id | unit_id | dt | courier_type | temp_category | subtotal_cents |
+----------+---------+------------+--------------+---------------+----------------+
| 1 | 101 | 2025-08-30 | biker | cold | 1800 |
| 2 | 101 | 2025-08-30 | biker | hot | 2400 |
| 3 | 101 | 2025-08-31 | car | cold | 2200 |
| 4 | 101 | 2025-09-01 | biker | cold | 2600 |
| 5 | 102 | 2025-08-30 | biker | cold | 2100 |
| 6 | 102 | 2025-08-31 | biker | hot | 1500 |
| 7 | 102 | 2025-09-01 | car | cold | 2000 |
| 8 | 103 | 2025-08-31 | biker | cold | 900 |
| 9 | 103 | 2025-09-01 | car | hot | 3000 |
| 10 | 103 | 2025-08-28 | biker | cold | 1700 |
+----------+---------+------------+--------------+---------------+----------------+
Tasks:
1) Non-aggregated condition percent (simple condition for numerator): Define is_high_value := (subtotal_cents >= 2000). For the 7-day window [2025-08-26, 2025-09-01], compute the percentage of orders that satisfy (courier_type='biker' AND temp_category='cold' AND is_high_value). Produce two variants of the denominator: V1 includes only orders whose unit_id had condition_label=1 on that same dt; V2 includes orders whose unit_id had condition_label in {0,1} on that dt (treat missing exposure rows as condition_label=0). Output both percentages with clearly labeled columns.
2) Aggregation-required condition percent: For each unit_id×dt in the same 7-day window, define is_power_day := (count of orders in the last 7 days inclusive for that unit with courier_type='biker' AND temp_category='cold' >= 2) AND (average subtotal_cents on those qualifying orders in the same 7-day window >= 2000). Compute the percentage of unit-days where is_power_day=TRUE under the same two denominator conventions as in (1): DV1 counts only unit-days with condition_label=1; DV2 counts unit-days with condition_label in {0,1}. Return a single row with DV1_percent and DV2_percent.
3) Multiple window frames and change: Build a unit-day series from the orders table (days with 0 orders must appear). For each unit_id and dt in [2025-08-26, 2025-09-01], compute: (a) orders_day := total orders that day; (b) cum_orders := SUM(orders_day) OVER (PARTITION BY unit_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); (c) rolling7 := SUM(orders_day) OVER (PARTITION BY unit_id ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); (d) pct_change_do_d := (orders_day - LAG(orders_day) OVER w1) / NULLIF(LAG(orders_day) OVER w1,0), where w1=(PARTITION BY unit_id ORDER BY dt); (e) non-overlapping_7day_change := (rolling7 - LAG(rolling7) OVER w7) / NULLIF(LAG(rolling7) OVER w7,0), where w7 uses ORDER BY dt with a frame ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING to represent the prior 7 days. Return one row per unit_id×dt with these columns.
4) Within-unit state changes vs partition: For each unit_id, define had_high_value_biker_cold_today := (exists an order on dt with courier_type='biker' AND temp_category='cold' AND subtotal_cents>=2000). For each change point where this boolean flips (0→1 or 1→0), output unit_id, dt_of_change, direction, delta_rolling7 := rolling7_after - rolling7_before (rolling7 defined on orders_day), and the percentile_rank of delta_rolling7 among all change deltas across units in the window using PERCENT_RANK() OVER (ORDER BY delta_rolling7).
Overview: This question evaluates proficiency with advanced SQL techniques—specifically the use of CTEs and multiple window functions, row-level versus aggregated conditionals, time-windowed aggregations, generation of unit-day series including zero-order days, and correct handling of missing exposure labels for percent and change calculations.
Read the full DoorDash Data Scientist interview experience this question came from
Order-level condition percentage with two denominator conventions
Use PostgreSQL.
Define is_high_value := (subtotal_cents >= 2000).
For the 7-day window from 2025-08-26 to 2025-09-01 (inclusive), compute the percentage of orders that satisfy:
- courier_type = 'biker'
- temp_category = 'cold'
- is_high_value
Produce two variants of the denominator:
- V1: denominator includes only orders whose unit_id had condition_label = 1 on that same dt (must match an exposures row with condition_label=1).
- V2: denominator includes orders whose unit_id had condition_label in {0,1} on that same dt, treating missing exposures rows as condition_label = 0 (i.e., include all orders in the window).
Return a single row with columns v1_percent and v2_percent (as percentages from 0 to 100).
Tables
exposures(unit_id INT, dt DATE, condition_label INT)
orders(order_id INT, unit_id INT, dt DATE, courier_type VARCHAR(20), temp_category VARCHAR(20), subtotal_cents INT)
Hints
- For V2, LEFT JOIN exposures and COALESCE(condition_label,0).
- Use FILTER on COUNT(*) to define numerator and denominators cleanly.
Unit-day power metric percentage with rolling 7-day conditions
Use PostgreSQL.
For the 7-day window from 2025-08-26 to 2025-09-01 (inclusive), build unit-day rows for each (unit_id, dt) in that range.
For each unit_id × dt, define is_power_day as:
- In the last 7 days inclusive for that unit (dt-6 through dt), consider only orders with courier_type='biker' AND temp_category='cold'.
- is_power_day = TRUE if BOTH:
1) the count of these qualifying orders in the 7-day window is >= 2
2) the average subtotal_cents of these qualifying orders in the same 7-day window is >= 2000
Compute the percentage of unit-days where is_power_day is TRUE under two denominator conventions:
- DV1: denominator includes only unit-days with condition_label = 1 in exposures on that dt.
- DV2: denominator includes unit-days with condition_label in {0,1}, treating missing exposures rows as condition_label = 0.
Return a single row with columns dv1_percent and dv2_percent (0 to 100).
Tables
exposures(unit_id INT, dt DATE, condition_label INT)
orders(order_id INT, unit_id INT, dt DATE, courier_type VARCHAR(20), temp_category VARCHAR(20), subtotal_cents INT)
Hints
- Build a unit-day grid with generate_series to ensure days with 0 qualifying orders are included.
- Use rolling window SUMs over daily counts and daily subtotal sums to compute rolling count and rolling average.
Unit-day series with cumulative, rolling-7, and change metrics
Use **PostgreSQL**.
From the `orders` table, build a complete **unit-day time series** so that days with **0 orders still appear** (a date spine joined to every unit). Cover every `unit_id` that appears in `orders` and every date `dt` from **2025-08-26 to 2025-09-01 inclusive** (a 7-day window).
For each `unit_id` x `dt`, compute these five metrics (use the **row-based** window frames below, ordered by `dt` within each `unit_id`):
- **`orders_day`** — number of orders placed by that unit on that day (0 on missing days).
- **`cum_orders`** — running cumulative total of `orders_day` from the start of the window through the current day: `SUM(orders_day) OVER (PARTITION BY unit_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`.
- **`rolling7`** — trailing 7-day sum of `orders_day` (current day plus the 6 preceding days): `SUM(orders_day) OVER (PARTITION BY unit_id ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
- **`pct_change_do_d`** — day-over-day fractional change of `orders_day` vs. the previous day for the same unit: `(orders_day - prev_orders_day) / prev_orders_day`, where `prev_orders_day = LAG(orders_day) OVER (PARTITION BY unit_id ORDER BY dt)`. Return `NULL` when there is no previous day **or** the previous day's value is 0 (use `NULLIF` to avoid divide-by-zero).
- **`non_overlapping_7day_change`** — fractional change of `rolling7` vs. its value **7 rows earlier** for the same unit: `(rolling7 - lag7) / lag7`, where `lag7 = LAG(rolling7, 7) OVER (PARTITION BY unit_id ORDER BY dt)`. Return `NULL` when there is no 7-rows-earlier value **or** that value is 0. (In this 7-day window there is never a row 7 positions back, so this column is `NULL` for every row.)
**Output:** one row per `unit_id` x `dt` with columns `unit_id, dt, orders_day, cum_orders, rolling7, pct_change_do_d, non_overlapping_7day_change`. Sort by `unit_id` ascending, then `dt` ascending.
Tables
orders(order_id INT, unit_id INT, dt DATE, courier_type VARCHAR(20), temp_category VARCHAR(20), subtotal_cents INT)
Hints
- Build a date spine with generate_series, CROSS JOIN it to the distinct units, then LEFT JOIN orders so 0-order days survive (COUNT of the order id gives 0).
- PostgreSQL won't let you nest a window aggregate inside LAG. Compute rolling7 in one CTE, then take LAG(rolling7, 7) in the next.
Detect within-unit boolean flips and rank change deltas
Use **PostgreSQL**.
You are given an `orders` table. For each `unit_id` and each calendar day `dt` in the inclusive window **2025-08-26 through 2025-09-01**, define a daily boolean and a rolling order count, then report the days on which the boolean flips.
First build a complete unit/day grid for every distinct `unit_id` in `orders` crossed with all 7 dates in the window (so days with **zero** orders still appear). For each cell define:
- **`orders_day`** = the number of orders for that `unit_id` on that `dt` (0 if none).
- **`had_high_value_biker_cold_today`** = TRUE if there exists at least one order on that `unit_id`/`dt` with `courier_type = 'biker'` AND `temp_category = 'cold'` AND `subtotal_cents >= 2000`; otherwise FALSE.
- **`rolling7`** = the trailing 7-day sum of `orders_day` within the unit, i.e. `SUM(orders_day) OVER (PARTITION BY unit_id ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
A **flip** for a unit is any day whose `had_high_value_biker_cold_today` differs from the previous day's value for the same unit (`0 -> 1` or `1 -> 0`). The first day in the window is never a flip (it has no previous day).
For every flip event, return one row with:
- `unit_id`
- `dt_of_change` — the `dt` on which the new state takes effect
- `direction` — the string `'0->1'` or `'1->0'`
- `delta_rolling7` — `rolling7` on `dt_of_change` minus `rolling7` on the previous day
- `percentile_rank` — `PERCENT_RANK() OVER (ORDER BY delta_rolling7)` computed over **all** flip events across all units in the window
Sort the result by `unit_id`, then by `dt_of_change` ascending.
Tables
exposures(unit_id INT, dt DATE, condition_label INT)
orders(order_id INT, unit_id INT, dt DATE, courier_type VARCHAR(20), temp_category VARCHAR(20), subtotal_cents INT)
Hints
- Build a full unit-by-day grid with generate_series and a CROSS JOIN so zero-order days still appear, then LEFT JOIN orders onto it.
- Use BOOL_OR(...) for the daily boolean and SUM(...) OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for rolling7.