SQL Problems II — Advanced

1As-of price join practiced

Range join · Point-in-time.

Prompt: Price each usage interval at the machine's price that was active when it ran. Prices change over time in price_history (non-overlapping validity windows).

SELECT u.event_id, u.machine_id, u.started_at,
       u.gpu_seconds / 3600.0 * p.price_per_hr AS cost
FROM usage_events u
JOIN price_history p
  ON p.machine_id = u.machine_id
 AND u.started_at >= p.valid_from
 AND u.started_at <  p.valid_to;

2Merge overlapping rental intervals practiced

Window · Interval merge / "sweep".

Prompt: Per machine, collapse overlapping or back-to-back rental intervals into continuous "busy" periods (to compute true occupied time without double-counting).

WITH ordered AS (
  SELECT machine_id, started_at, ended_at,
    MAX(ended_at) OVER (PARTITION BY machine_id ORDER BY started_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
  FROM rentals
),
flagged AS (
  SELECT *, CASE WHEN prev_max_end IS NULL OR started_at > prev_max_end
                 THEN 1 ELSE 0 END AS new_island
  FROM ordered
),
grouped AS (
  SELECT *, SUM(new_island) OVER (PARTITION BY machine_id ORDER BY started_at) AS grp
  FROM flagged
)
SELECT machine_id, MIN(started_at) AS busy_start, MAX(ended_at) AS busy_end
FROM grouped GROUP BY machine_id, grp;

3Rolling 30-day active customers practiced

Aggregate · Rolling distinct count.

Prompt: For each day, how many distinct customers were active in the trailing 30 days? (Distinct counts don't compose with a simple window SUM — that's the trap.)

WITH daily AS (
  SELECT DISTINCT customer_id, DATE(started_at) AS day FROM usage_events
),
spine AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
)
SELECT s.day, COUNT(DISTINCT d.customer_id) AS rolling_30d_active
FROM spine s
JOIN daily d ON d.day > s.day - 30 AND d.day <= s.day
GROUP BY s.day
ORDER BY s.day;

4Interruptions & time-to-resume practiced

Window · State machine.

Prompt: From instance_state_log (states: running, stopped), count interruptions per instance and the median time from a stop to the next run.

WITH seq AS (
  SELECT instance_id, state, changed_at,
    LEAD(state)      OVER (PARTITION BY instance_id ORDER BY changed_at) AS next_state,
    LEAD(changed_at) OVER (PARTITION BY instance_id ORDER BY changed_at) AS next_at
  FROM instance_state_log
),
interruptions AS (
  SELECT instance_id, changed_at AS stopped_at, next_at AS resumed_at
  FROM seq
  WHERE state = 'stopped' AND next_state = 'running'   -- a true resume
)
SELECT instance_id,
       COUNT(*) AS interruptions,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY resumed_at - stopped_at) AS median_resume
FROM interruptions GROUP BY instance_id;

5Time-boxed funnel practiced

Join · Funnel with windows.

Prompt: Of customers who ran a search, how many placed a bid within 1 hour, and of those, how many had a running instance within 1 hour of the bid? Counts per stage.

WITH first_search AS (
  SELECT customer_id, MIN(searched_at) AS t0 FROM searches GROUP BY customer_id
),
bid AS (
  SELECT f.customer_id, MIN(b.placed_at) AS t1
  FROM first_search f
  JOIN bids b ON b.customer_id=f.customer_id
             AND b.placed_at BETWEEN f.t0 AND f.t0 + INTERVAL '1 hour'
  GROUP BY f.customer_id
),
ran AS (
  SELECT bd.customer_id
  FROM bid bd
  JOIN instance_state_log s ON s.customer_id=bd.customer_id AND s.state='running'
             AND s.changed_at BETWEEN bd.t1 AND bd.t1 + INTERVAL '1 hour'
  GROUP BY bd.customer_id
)
SELECT (SELECT COUNT(*) FROM first_search) AS searched,
       (SELECT COUNT(*) FROM bid)          AS bid_1h,
       (SELECT COUNT(*) FROM ran)          AS ran_1h;

6Retention curve (Nx) practiced

Window · Cohort rate.

Prompt: For each signup-week cohort, output the % of customers active in week 0..8 after signup (a triangle/retention curve).

WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('week', signed_up_at) AS wk0 FROM customers
),
size AS (SELECT wk0, COUNT(*) n FROM cohort GROUP BY wk0),
act AS (
  SELECT DISTINCT c.wk0,
    (DATE_TRUNC('week', u.started_at)::date - c.wk0::date) / 7 AS wk_offset,
    u.customer_id
  FROM cohort c JOIN usage_events u USING (customer_id)
)
SELECT a.wk0, a.wk_offset,
       COUNT(DISTINCT a.customer_id) AS active,
       ROUND(100.0*COUNT(DISTINCT a.customer_id)/s.n, 1) AS pct
FROM act a JOIN size s ON s.wk0=a.wk0
WHERE a.wk_offset BETWEEN 0 AND 8
GROUP BY a.wk0, a.wk_offset, s.n
ORDER BY a.wk0, a.wk_offset;

7Dedup with conflict resolution practiced

Window · Survivorship.

Prompt: A customer record arrives from 3 sources with conflicting fields. Produce one golden row per customer: most-recent non-null value per field (not just the latest whole row).

-- "Carry forward the latest non-null per field" survivorship
SELECT customer_id,
  (ARRAY_AGG(email      ORDER BY updated_at DESC) FILTER (WHERE email      IS NOT NULL))[1] AS email,
  (ARRAY_AGG(country    ORDER BY updated_at DESC) FILTER (WHERE country    IS NOT NULL))[1] AS country,
  (ARRAY_AGG(plan_tier  ORDER BY updated_at DESC) FILTER (WHERE plan_tier  IS NOT NULL))[1] AS plan_tier
FROM customer_sources
GROUP BY customer_id;

8Sessions with a duration cap practiced

Window · Constrained sessionization.

Prompt: Sessionize events with a 30-min inactivity gap and a hard cap: no session longer than 2 hours (a long continuous stream still splits every 2h).

WITH lagged AS (
  SELECT customer_id, event_ts,
    LAG(event_ts) OVER (PARTITION BY customer_id ORDER BY event_ts) AS prev
  FROM events
),
gapflag AS (
  SELECT *, CASE WHEN prev IS NULL OR event_ts-prev > INTERVAL '30 min' THEN 1 ELSE 0 END AS gap_new
  FROM lagged
),
gapsess AS (
  SELECT *, SUM(gap_new) OVER (PARTITION BY customer_id ORDER BY event_ts) AS gap_sid
  FROM gapflag
)
SELECT *,
  gap_sid::text || '-' ||
  FLOOR(EXTRACT(EPOCH FROM (event_ts -
        MIN(event_ts) OVER (PARTITION BY customer_id, gap_sid))) / 7200)::text AS session_id
FROM gapsess;

9Price deciles per GPU type practiced

Window · Distribution.

Prompt: For each GPU type, bucket current listed machines into price deciles and return the boundary price of each decile (for a pricing-competitiveness view).

WITH d AS (
  SELECT gpu_type, price_per_hr,
    NTILE(10) OVER (PARTITION BY gpu_type ORDER BY price_per_hr) AS decile
  FROM listings WHERE status = 'available'
)
SELECT gpu_type, decile,
       MIN(price_per_hr) AS decile_low,
       MAX(price_per_hr) AS decile_high,
       COUNT(*)          AS n
FROM d GROUP BY gpu_type, decile
ORDER BY gpu_type, decile;

10Z-score anomaly detection practiced

Window · Statistical test in SQL.

Prompt: Flag days where total GPU-hours deviate > 3 standard deviations from the trailing 28-day mean (excluding the day itself).

WITH daily AS (
  SELECT DATE(started_at) AS day, SUM(gpu_seconds)/3600.0 AS gpu_hours
  FROM usage_events GROUP BY 1
),
stats AS (
  SELECT day, gpu_hours,
    AVG(gpu_hours)    OVER w AS mean_28,
    STDDEV(gpu_hours) OVER w AS sd_28
  FROM daily
  WINDOW w AS (ORDER BY day ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
)
SELECT day, gpu_hours, mean_28, sd_28,
       (gpu_hours - mean_28) / NULLIF(sd_28,0) AS z
FROM stats
WHERE sd_28 > 0 AND ABS((gpu_hours - mean_28)/sd_28) > 3
ORDER BY day;

11Gap-fill + carry-forward practiced

Spine · LOCF.

Prompt: Produce a continuous daily series of each machine's listed price, carrying the last known price forward over days with no price change.

WITH spine AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
),
m AS (SELECT DISTINCT machine_id FROM price_changes),
grid AS (SELECT m.machine_id, s.day FROM m CROSS JOIN spine s),
joined AS (
  SELECT g.machine_id, g.day, pc.new_price
  FROM grid g
  LEFT JOIN price_changes pc
    ON pc.machine_id=g.machine_id AND DATE(pc.changed_at)=g.day
)
SELECT machine_id, day,
  LAST_VALUE(new_price IGNORE NULLS) OVER (
    PARTITION BY machine_id ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS price_locf
FROM joined;

12Median clearing bid per hour practiced

Aggregate · Market price discovery.

Prompt: For each GPU type and hour, what was the median winning bid price (the market-clearing price)? Only bids that actually won count.

SELECT m.gpu_type,
       DATE_TRUNC('hour', b.placed_at) AS hour,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY b.bid_price) AS median_clearing_price,
       COUNT(*) AS winning_bids
FROM bids b
JOIN machines m ON m.machine_id = b.machine_id
WHERE b.won = true
GROUP BY m.gpu_type, DATE_TRUNC('hour', b.placed_at)
ORDER BY gpu_type, hour;

✓ Check yourself

Answer from memory before opening each one.

1. In problem 2, one machine has rentals 09:00 to 10:00, 09:30 to 09:45, 10:00 to 11:00 and 12:00 to 13:00. What busy periods come back, and why would comparing each row only with the previous row's end time fail?

   Show answer

   The periods are 09:00 to 11:00 and 12:00 to 13:00; the third rental starts exactly when the running maximum end (10:00) is reached, so it joins the first. Comparing with the previous row's end (09:45) would wrongly start a new period at 10:00, because a short interval nested inside a long one hides the true end. See merging overlapping intervals.

2. A teammate computes rolling 30-day actives by summing each day's distinct customer count over a 30-day window. A customer active on five of the thirty days is in the result how many times, and what is the fix?

   Show answer

   Five times, so the figure overstates. Distinct counts do not add across days. Count distinct customers over the range directly, with a self-join on the date range, or merge daily HyperLogLog sketches at scale. See rolling 30-day actives.

3. A retention query is correct. Someone removes the DISTINCT from the inner activity CTE and week 4 now reads 140%. Why does a rate above 100% appear?

   Show answer

   Without DISTINCT, each usage row survives as its own row, so the numerator counts events, not customers. The denominator is still the cohort's customer count. Count distinct customers per cohort and week offset, then divide. See the retention curve.

4. A customer has two source rows. One dated March has email a@example.com and a null country. A later one dated April has a null email and country DE. What does latest-whole-row return, and what does the field-level query return?

   Show answer

   Latest-whole-row returns a null email with country DE. The field-level query returns a@example.com with DE, taking the newest non-null value for each field. This is the survivorship rule you want when sources leave fields empty. See dedup with conflict resolution.

5. Run problem 8 on events every 20 minutes from 00:00 to 02:20, so no 30-minute gap ever occurs. With the 2-hour cap, how many sessions do you get, and where does the split fall?

   Show answer

   Two sessions. All eight events share one gap-based group, so the cap alone splits them: elapsed time below 2 hours stays in bucket 0 (00:00 to 01:40), and 02:00 and 02:20 fall in bucket 1. Two layered rules need two layered groupings. See sessions with a duration cap.

6. Problem 10 excludes the current day from its own 28-day baseline. What goes wrong if you include it, and what two other cases make the z-score unreliable?

   Show answer

   A spike inflates its own mean and standard deviation, so the z-score shrinks and the anomaly can pass unnoticed. The score also breaks on a flat series, where the standard deviation is zero, and on the first days of data, where the window is short. Guard both, and compare like weekdays if weekly seasonality is strong. See z-score anomaly detection.

Takeaway

• Merge intervals against the running maximum of earlier end times, not the previous row's end; a new period starts only when a row begins after that maximum.
• Distinct counts do not add across days; a rolling distinct count needs a range join, or mergeable sketches at scale.
• A retention rate divides distinct active customers by the cohort's own size; counting usage rows inflates it.
• When the latest row has nulls, take the newest non-null value per field instead of the whole latest row.
• Two session rules, such as a gap and a cap, are two layered groupings, not one.
• Keep the current row out of its own anomaly baseline, and guard zero variance and short history.
• A point-in-time join is safe only if validity windows do not overlap.

SQL is one skill among three; the next is moving data reliably, so turn to the pipelines. → Data Pipelines

Further reading

• Window Functions — PostgreSQL docs, 2026. Running max, LEAD and LAG behaviour used in problems 2, 4 and 8.
• Table Expressions — PostgreSQL docs, 2026. Range joins and LATERAL, the basis of problems 1 and 3.
• Aggregate Functions — PostgreSQL docs, 2026. ARRAY_AGG with FILTER for survivorship, and ordered-set percentiles.
• ASOF JOIN — Snowflake docs, 2026. The native form of problem 1.
• FROM and JOIN clauses — DuckDB docs, 2026. As-of joins you can run locally against sample data.
• Approximate aggregate functions — Google Cloud docs, 2026. HLL sketches for the scale answer to problem 3.