cswork / libraryOpen practice workspace

Coding questions · DoorDash

Write SQL for percent and window changes

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.

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).

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).

Table exposures

columntypeconstraints
unit_idINTNOT NULL
dtDATENOT NULL
condition_labelINTNOT NULL

Sample data:

condition_labeldtunit_id
12025-08-30101
12025-08-31101
02025-09-01101
02025-08-30102
12025-08-31102
12025-09-01102
02025-08-31103
02025-09-01103

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
unit_idINTNOT NULL
dtDATENOT NULL
courier_typeVARCHAR(20)NOT NULL
temp_categoryVARCHAR(20)NOT NULL
subtotal_centsINTNOT NULL

Sample data:

courier_typedtorder_idsubtotal_centstemp_categoryunit_id
biker2025-08-3011800cold101
biker2025-08-3022400hot101
car2025-08-3132200cold101
biker2025-09-0142600cold101
biker2025-08-3052100cold102
biker2025-08-3161500hot102
car2025-09-0172000cold102
biker2025-08-318900cold103
car2025-09-0193000hot103
biker2025-08-28101700cold103

Expected output

Single row with two percentage columns: V1 uses only condition_label=1 exposure-days as denominator; V2 treats missing exposure rows as condition_label=0 and includes all orders in the date window.

v1_percentv2_percent
020

Hints

  1. For V2, LEFT JOIN exposures and COALESCE(condition_label,0).
  2. 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).

Table exposures

columntypeconstraints
unit_idINTNOT NULL
dtDATENOT NULL
condition_labelINTNOT NULL

Sample data:

condition_labeldtunit_id
12025-08-30101
12025-08-31101
02025-09-01101
02025-08-30102
12025-08-31102
12025-09-01102
02025-08-31103
02025-09-01103

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
unit_idINTNOT NULL
dtDATENOT NULL
courier_typeVARCHAR(20)NOT NULL
temp_categoryVARCHAR(20)NOT NULL
subtotal_centsINTNOT NULL

Sample data:

courier_typedtorder_idsubtotal_centstemp_categoryunit_id
biker2025-08-3011800cold101
biker2025-08-3022400hot101
car2025-08-3132200cold101
biker2025-09-0142600cold101
biker2025-08-3052100cold102
biker2025-08-3161500hot102
car2025-09-0172000cold102
biker2025-08-318900cold103
car2025-09-0193000hot103
biker2025-08-28101700cold103

Expected output

Single row with dv1_percent (only exposure-days with condition_label=1) and dv2_percent (all unit-days in the date grid, missing exposures treated as condition_label=0).

dv1_percentdv2_percent
04.76

Hints

  1. Build a unit-day grid with generate_series to ensure days with 0 qualifying orders are included.
  2. 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.

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
unit_idINTNOT NULL
dtDATENOT NULL
courier_typeVARCHAR(20)NOT NULL
temp_categoryVARCHAR(20)NOT NULL
subtotal_centsINTNOT NULL

Sample data:

courier_typedtorder_idsubtotal_centstemp_categoryunit_id
biker2025-08-3011800cold101
biker2025-08-3022400hot101
car2025-08-3132200cold101
biker2025-09-0142600cold101
biker2025-08-3052100cold102
biker2025-08-3161500hot102
car2025-09-0172000cold102
biker2025-08-318900cold103
car2025-09-0193000hot103
biker2025-08-28101700cold103

Expected output

One row per unit_id x date (2025-08-26..2025-09-01), including 0-order days, with orders_day, running cum_orders, trailing rolling7, day-over-day pct change, and the 7-rows-back rolling change (NULL throughout this 7-day window).

cum_ordersdtnon_overlapping_7day_changeorders_daypct_change_do_drolling7unit_id
02025-08-26NULL0NULL0101
02025-08-27NULL0NULL0101
02025-08-28NULL0NULL0101
02025-08-29NULL0NULL0101
22025-08-30NULL2NULL2101
32025-08-31NULL1-0.500000000000000000003101
42025-09-01NULL10E-204101
02025-08-26NULL0NULL0102
02025-08-27NULL0NULL0102
02025-08-28NULL0NULL0102
02025-08-29NULL0NULL0102
12025-08-30NULL1NULL1102
22025-08-31NULL10E-202102
32025-09-01NULL10E-203102
02025-08-26NULL0NULL0103
02025-08-27NULL0NULL0103
12025-08-28NULL1NULL1103
12025-08-29NULL0-1.000000000000000000001103
12025-08-30NULL0NULL1103
22025-08-31NULL1NULL2103
32025-09-01NULL10E-203103

Hints

  1. 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).
  2. PostgreSQL won't let you nest a window aggregate inside LAG. Compute rolling7 in one CTE, then take LAG(rolling7, 7) in the next.
  3. Cast the numerator to ::numeric for fractional division and wrap the denominator in NULLIF(..., 0) to dodge divide-by-zero.

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.

Table exposures

columntypeconstraints
unit_idINTNOT NULL
dtDATENOT NULL
condition_labelINTNOT NULL

Sample data:

condition_labeldtunit_id
12025-08-30101
12025-08-31101
02025-09-01101
02025-08-30102
12025-08-31102
12025-09-01102
02025-08-31103
02025-09-01103

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
unit_idINTNOT NULL
dtDATENOT NULL
courier_typeVARCHAR(20)NOT NULL
temp_categoryVARCHAR(20)NOT NULL
subtotal_centsINTNOT NULL

Sample data:

courier_typedtorder_idsubtotal_centstemp_categoryunit_id
biker2025-08-3011800cold101
biker2025-08-3022400hot101
car2025-08-3132200cold101
biker2025-09-0142600cold101
biker2025-08-3052100cold102
biker2025-08-3161500hot102
car2025-09-0172000cold102
biker2025-08-318900cold103
car2025-09-0193000hot103
biker2025-08-28101700cold103

Expected output

One row per within-unit flip of had_high_value_biker_cold_today, with the rolling7 delta and its percent rank among all flip deltas.

delta_rolling7directiondt_of_changepercentile_rankunit_id
10->12025-09-010101
10->12025-08-300102
11->02025-08-310102

Hints

  1. 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.
  2. Use BOOL_OR(...) for the daily boolean and SUM(...) OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for rolling7.
  3. PostgreSQL won't let you wrap LAG() around a SUM() OVER() in one step — compute rolling7 in one CTE, then take LAG(rolling7) in the next.