Drill · Solutions Hidden

SQL Problems Worked Out

12 problems covering the patterns interviewers actually test, with a usage-marketplace flavor. Turn on drill mode, try each on a timer, then reveal. Progress saves locally.

🎯 12 problems 🐘 Postgres / Snowflake / BigQuery dialects ⏱ 15-25 min each 🔁 Progress saved locally
0 / 12 practiced
Schema you'll use

instances(instance_id, machine_id, customer_id, region, status, started_at, ended_at) · usage_events(event_id, customer_id, instance_id, gpu_seconds, started_at) · invoices(invoice_id, customer_id, amount, billing_month, status) · customers(customer_id, region, signed_up_at)

1Latest state per instance

Window · The single most common pattern.

Prompt: instance_status_log(instance_id, status, changed_at) records every status change. Return the current status of each instance.

Approach — ROW_NUMBER (or QUALIFY)
p1.sql
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY instance_id
                       ORDER BY changed_at DESC, status DESC) AS rn
  FROM instance_status_log
)
SELECT instance_id, status, changed_at FROM ranked WHERE rn = 1;

-- Snowflake/BigQuery:
-- SELECT * FROM instance_status_log
-- QUALIFY ROW_NUMBER() OVER (PARTITION BY instance_id ORDER BY changed_at DESC) = 1;

Watch: deterministic tiebreaker so "latest" is stable. Don't use MAX(changed_at) + self-join — it breaks on ties and is slower.

2Top-N customers per region

Window · Top-N per group.

Prompt: Return the top 3 customers by total paid invoice amount within each region.

Approach — aggregate then RANK
p2.sql
WITH spend AS (
  SELECT c.region, c.customer_id, SUM(i.amount) AS total
  FROM customers c
  JOIN invoices i ON i.customer_id = c.customer_id AND i.status = 'paid'
  GROUP BY c.region, c.customer_id
),
ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY region ORDER BY total DESC) AS rnk
  FROM spend
)
SELECT region, customer_id, total FROM ranked WHERE rnk <= 3
ORDER BY region, total DESC;

Watch: ROW_NUMBER picks exactly 3 (arbitrary tiebreak); DENSE_RANK includes ties at 3rd. Clarify which they want.

3Running monthly revenue

Window · Running total.

Prompt: Show paid revenue per month and a cumulative running total over time.

Approach — aggregate then windowed SUM
p3.sql
WITH monthly AS (
  SELECT billing_month, SUM(amount) AS revenue
  FROM invoices WHERE status = 'paid'
  GROUP BY billing_month
)
SELECT billing_month, revenue,
  SUM(revenue) OVER (ORDER BY billing_month
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM monthly
ORDER BY billing_month;

Watch: aggregate to the grain first, then apply the window. Specify ROWS to avoid RANGE peer-lumping.

4Consecutive active days

Window · Gaps and islands.

Prompt: daily_active(customer_id, active_date) has one row per active day. Find each customer's longest streak of consecutive active days.

Approach — date minus row number
p4.sql
WITH g AS (
  SELECT customer_id, active_date,
    active_date - (ROW_NUMBER() OVER (PARTITION BY customer_id
                                      ORDER BY active_date))::int AS grp
  FROM daily_active
),
streaks AS (
  SELECT customer_id, grp, COUNT(*) AS len
  FROM g GROUP BY customer_id, grp
)
SELECT customer_id, MAX(len) AS longest_streak
FROM streaks GROUP BY customer_id;

Why it works: consecutive dates increase by 1, the row number also increases by 1, so their difference is constant across an unbroken run. Be ready to explain that.

5Sessionize usage events

Window · Sessionization.

Prompt: Group each customer's usage_events into sessions, where a gap > 30 minutes starts a new session.

Approach — flag boundary, running sum
p5.sql
WITH lagged AS (
  SELECT customer_id, event_id, started_at,
    LAG(started_at) OVER (PARTITION BY customer_id ORDER BY started_at) AS prev
  FROM usage_events
),
flagged AS (
  SELECT *, CASE WHEN prev IS NULL
                   OR started_at - prev > INTERVAL '30 minutes'
                 THEN 1 ELSE 0 END AS is_new
  FROM lagged
)
SELECT customer_id, event_id, started_at,
  SUM(is_new) OVER (PARTITION BY customer_id ORDER BY started_at) AS session_id
FROM flagged;

Pattern: flag boundaries, running-sum the flag to mint IDs. Generalizes widely.

6Dedup with tiebreaker

Window · Dedup.

Prompt: A raw events feed has duplicate rows for the same event_id from at-least-once delivery. Keep one row per event_id — the most recently ingested.

Approach — ROW_NUMBER on the dedup key
p6.sql
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY event_id
                       ORDER BY ingested_at DESC) AS rn
  FROM raw_events
)
SELECT * EXCEPT(rn)   -- BigQuery; else list columns
FROM ranked WHERE rn = 1;

First-hire note: say you'd push this dedup into the staging model so every downstream consumer gets clean, idempotent data — not re-solve it per query. Ties to idempotency.

7Cohort retention

JoinWindow · Cohorts.

Prompt: Group customers by signup month. For each cohort, show how many were still active (had usage) in months 0, 1, 2 after signup.

Approach — cohort month + month offset
p7.sql
WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('month', signed_up_at) AS cohort_month
  FROM customers
),
activity AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('month', started_at) AS active_month
  FROM usage_events
),
joined AS (
  SELECT c.cohort_month, c.customer_id,
    (EXTRACT(YEAR FROM a.active_month) - EXTRACT(YEAR FROM c.cohort_month)) * 12
      + (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM c.cohort_month))
      AS month_offset
  FROM cohort c JOIN activity a ON a.customer_id = c.customer_id
)
SELECT cohort_month, month_offset, COUNT(DISTINCT customer_id) AS retained
FROM joined
WHERE month_offset BETWEEN 0 AND 2
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Watch: divide retained by cohort size for a rate. Mention you'd build this as a model, not re-derive it ad hoc.

8Median GPU-hours

Aggregate · Percentiles.

Prompt: Find the median GPU-hours consumed per customer last month.

Approach — PERCENTILE_CONT (and the portable fallback)
p8.sql
WITH per_customer AS (
  SELECT customer_id, SUM(gpu_seconds)/3600.0 AS gpu_hours
  FROM usage_events
  WHERE started_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
    AND started_at <  DATE_TRUNC('month', CURRENT_DATE)
  GROUP BY customer_id
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gpu_hours) AS median_hours
FROM per_customer;

-- Portable fallback if PERCENTILE_CONT is unavailable:
-- rank rows, pick the middle one(s) with ROW_NUMBER + COUNT.

Note: mention median over mean because usage is heavily right-skewed (a few whales dominate the average).

9Month-over-month growth

Window · LAG.

Prompt: For each month, show revenue, prior-month revenue, and MoM % change.

Approach — LAG with divide-by-zero guard
p9.sql
WITH monthly AS (
  SELECT billing_month, SUM(amount) AS rev
  FROM invoices WHERE status='paid' GROUP BY billing_month
)
SELECT billing_month, rev,
  LAG(rev) OVER (ORDER BY billing_month) AS prev_rev,
  ROUND(100.0*(rev - LAG(rev) OVER (ORDER BY billing_month))
        / NULLIF(LAG(rev) OVER (ORDER BY billing_month),0), 1) AS mom_pct
FROM monthly ORDER BY billing_month;

Watch: NULLIF(...,0) prevents divide-by-zero; the first month's % is correctly NULL.

10Signup → paid funnel

Aggregate · Funnel.

Prompt: Of customers who signed up, how many launched a first instance, and how many produced a paid invoice? Show counts and conversion rates.

Approach — boolean flags via EXISTS, then aggregate
p10.sql
WITH flags AS (
  SELECT c.customer_id,
    EXISTS(SELECT 1 FROM instances i WHERE i.customer_id=c.customer_id) AS launched,
    EXISTS(SELECT 1 FROM invoices v
           WHERE v.customer_id=c.customer_id AND v.status='paid')       AS paid
  FROM customers c
)
SELECT
  COUNT(*)                                   AS signed_up,
  COUNT(*) FILTER (WHERE launched)           AS launched,
  COUNT(*) FILTER (WHERE paid)               AS paid,
  ROUND(100.0*COUNT(*) FILTER (WHERE launched)/COUNT(*),1) AS pct_launched,
  ROUND(100.0*COUNT(*) FILTER (WHERE paid)
        / NULLIF(COUNT(*) FILTER (WHERE launched),0),1)    AS pct_launched_to_paid
FROM flags;

Watch: decide whether each stage requires the previous (strict funnel) or is independent. Clarify before coding.

11Daily fleet utilization

Join · Date spine + ratio.

Prompt: For each day, compute utilization = rented GPU-hours / available GPU-hours across the fleet. Days with zero rentals must still appear (as 0%).

Approach — generate a date spine, LEFT JOIN usage
p11.sql
WITH days AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
),
rented AS (
  SELECT DATE(started_at) AS day, SUM(gpu_seconds)/3600.0 AS rented_hours
  FROM usage_events GROUP BY 1
),
capacity AS (   -- e.g. 500 GPUs * 24h available per day
  SELECT 500 * 24.0 AS available_hours
)
SELECT d.day,
  COALESCE(r.rented_hours,0) AS rented_hours,
  ROUND(100.0*COALESCE(r.rented_hours,0)/cap.available_hours,1) AS utilization_pct
FROM days d
CROSS JOIN capacity cap
LEFT JOIN rented r ON r.day = d.day
ORDER BY d.day;

Why the spine: a plain GROUP BY drops zero-rental days; the LEFT JOIN against a generated calendar guarantees a continuous series. This is the canonical "no gaps in the time axis" trick.

12Billing reconciliation

JoinAggregate · Reconciliation / data quality.

Prompt: Find customers whose metered usage (priced) disagrees with what was invoiced for a given month by more than $1. This is a real first-hire task: catch billing leakage.

Approach — aggregate both sides to same grain, FULL OUTER JOIN, compare
p12.sql
WITH metered AS (
  SELECT customer_id, DATE_TRUNC('month', started_at) AS month,
         SUM(gpu_seconds)/3600.0 * 1.20 AS expected_amount
  FROM usage_events GROUP BY 1,2
),
invoiced AS (
  SELECT customer_id, billing_month AS month, SUM(amount) AS invoiced_amount
  FROM invoices GROUP BY 1,2
)
SELECT
  COALESCE(m.customer_id, i.customer_id) AS customer_id,
  COALESCE(m.month, i.month)             AS month,
  COALESCE(m.expected_amount,0)          AS expected_amount,
  COALESCE(i.invoiced_amount,0)          AS invoiced_amount,
  COALESCE(i.invoiced_amount,0) - COALESCE(m.expected_amount,0) AS diff
FROM metered m
FULL OUTER JOIN invoiced i
  ON m.customer_id = i.customer_id AND m.month = i.month
WHERE ABS(COALESCE(i.invoiced_amount,0) - COALESCE(m.expected_amount,0)) > 1
ORDER BY ABS(COALESCE(i.invoiced_amount,0) - COALESCE(m.expected_amount,0)) DESC;

Why FULL OUTER: it catches both directions — metered-but-not-invoiced (lost revenue) and invoiced-but-not-metered (over-charge). Aggregate both sides to identical grain before joining or you'll fan out. This query is a data-quality check; in 05 we turn it into an automated test.

Next

Ready for harder problems? 03b — SQL Problems II (Advanced) →