SQL Problems Worked Out

1Latest state per instance practiced

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.

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;

2Top-N customers per region practiced

Window · Top-N per group.

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

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;

3Running monthly revenue practiced

Window · Running total.

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

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;

4Consecutive active days practiced

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.

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;

5Sessionize usage events practiced

Window · Sessionization.

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

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;

6Dedup with tiebreaker practiced

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.

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;

7Cohort retention practiced

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.

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;

8Median GPU-hours practiced

Aggregate · Percentiles.

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

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.

9Month-over-month growth practiced

Window · LAG.

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

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;

10Signup → paid funnel practiced

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.

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;

11Daily fleet utilization practiced

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

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;

12Billing reconciliation practiced

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.

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;

✓ Check yourself

Answer from memory before opening each one.

1. In problem 1, two status rows for one instance share the same changed_at, and the query breaks the tie with status DESC. The result is now stable between runs. Is it right?

   Show answer

   Stable is not the same as correct. Sorting on the status text picks 'stopped' over 'running' because of the alphabet, not because it happened last. Ask the source what orders its events, such as a sequence number or ingest id, and sort on that. See latest state per instance.

2. Problem 2 ranks customers with DENSE_RANK and keeps rank 3 or better. Region EU has paid totals of 900, 800, 800, 700 and 600. How many EU rows come back, and how many would ROW_NUMBER return?

   Show answer

   DENSE_RANK gives ranks 1, 2, 2, 3, so four rows return: 900, both 800s and 700. ROW_NUMBER returns exactly three rows and picks one of the two 800s arbitrarily. Ask the requester which behaviour they want before you choose. See top-N per region.

3. Run the sessionization from problem 5 on events at 10:00, 10:20, 10:55 and 11:40 with a 30-minute gap rule. What do is_new and session_id contain for the four rows?

   Show answer

   is_new is 1, 0, 1, 1 because the gaps are first row, 20, 35 and 45 minutes. The running sum gives session_id 1, 1, 2, 3, so the events form three sessions. If you see one session per row, the gap test is firing on every row. See sessionize usage events.

4. Your problem 11 utilization chart shows 0% for three days in a row. The date spine is doing its job. What else must you check before telling anyone that nothing was rented?

   Show answer

   The spine makes empty days appear, but it cannot tell zero rentals from a collector that was down. Check freshness and row volume on usage_events for those days, and show missing data as a gap instead of 0%. See daily fleet utilization.

5. A customer has $50.00 of metered usage in March and no invoice row at all. Does the reconciliation in problem 12 return that customer, and what would an INNER JOIN have done?

   Show answer

   It returns the customer. The FULL OUTER JOIN keeps the metered row, invoiced_amount is coalesced to 0, and the $50 difference exceeds the $1 threshold. An INNER JOIN would drop the customer silently, hiding exactly the lost-revenue case you are looking for. See billing reconciliation.

6. Problem 8 reports a median of 14 GPU-hours while the mean is 120. Which do you give leadership as the typical customer, and what do you add?

   Show answer

   The median. A few very large customers pull the mean far above what most customers use. Add the share of total hours that the top few customers account for, so the skew is visible rather than hidden. See median GPU-hours.

Takeaway

• Latest-per-group is ROW_NUMBER with a tiebreaker, and a stable tiebreaker is not necessarily the right one; ask what the source uses to order events.
• Top-N needs a decision on ties: DENSE_RANK keeps them, ROW_NUMBER cuts arbitrarily.
• Sessions and streaks use the same move: mark a boundary, then number the groups with a running sum or a subtracted row number.
• Aggregate to the result grain first, then apply the window.
• A date spine keeps empty days in the series, but it cannot tell zero activity from missing data.
• Reconcile with both sides at the same grain and a FULL OUTER JOIN, so lost revenue and over-charges both appear.
• For skewed usage, report the median and the share held by the largest customers, not the mean alone.

The foundational set is done; the advanced set adds interval merging, rolling distinct counts and anomaly detection. → SQL Problems II

Further reading

• Tutorial: Window Functions — PostgreSQL docs, 2026. The mechanics behind problems 1 to 6.
• Window Functions — PostgreSQL docs, 2026. How ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD treat ties and partitions.
• Aggregate Functions — PostgreSQL docs, 2026. FILTER, PERCENTILE_CONT and the other ordered-set aggregates used in problems 8 and 10.
• Date/Time Functions and Operators — PostgreSQL docs, 2026. DATE_TRUNC, EXTRACT and interval arithmetic for cohorts and spines.
• Subquery Expressions — PostgreSQL docs, 2026. EXISTS semantics behind the funnel flags in problem 10.