SQL Problems
Ten drillable problems — five creation, five debugging — flavored to a GPU-marketplace schema. Multiple approaches per problem with tradeoff discussion.
customers(customer_id, signed_up_at, country, plan)
gpu_sessions(session_id, customer_id, gpu_id, tier, started_at, ended_at, revenue)
gpus(gpu_id, supplier_id, tier, hourly_cost)
suppliers(supplier_id, name, joined_at, country)
A · Creation problems
P1. Top 3 customers by GPU-hours per week, last 8 weeks
Return (week, customer_id, gpu_hours, rank) for the top 3 customers by GPU-hours each ISO week for the last 8 weeks.
Show solution
WITH per_week AS (
SELECT
DATE_TRUNC('week', started_at)::DATE AS week,
customer_id,
SUM(EXTRACT(EPOCH FROM ended_at - started_at) / 3600.0) AS gpu_hours
FROM gpu_sessions
WHERE started_at >= NOW() - INTERVAL '8 weeks'
GROUP BY 1, 2
),
ranked AS (
SELECT
week, customer_id, gpu_hours,
DENSE_RANK() OVER (PARTITION BY week ORDER BY gpu_hours DESC) AS rk
FROM per_week
)
SELECT week, customer_id, gpu_hours, rk
FROM ranked
WHERE rk <= 3
ORDER BY week, rk;Tradeoff: DENSE_RANK vs ROW_NUMBER — ties keep all tied customers (could exceed 3 per week). ROW_NUMBER caps at exactly 3 but breaks ties arbitrarily. Ask the interviewer; default to DENSE_RANK + caveat.
P2. Weekly cohort retention to week N
For each signup-week cohort in the last 12 weeks, report retention rate at weeks 1, 2, 4, 8 (customer "retained" if they launched at least one session that week).
Show solution
WITH cohorts AS (
SELECT customer_id, DATE_TRUNC('week', signed_up_at)::DATE AS cohort_week
FROM customers
WHERE signed_up_at >= NOW() - INTERVAL '12 weeks'
),
cohort_size AS (
SELECT cohort_week, COUNT(*) AS size FROM cohorts GROUP BY 1
),
activity AS (
SELECT DISTINCT
g.customer_id,
DATE_TRUNC('week', g.started_at)::DATE AS active_week
FROM gpu_sessions g
),
retained AS (
SELECT
c.cohort_week,
(EXTRACT(EPOCH FROM a.active_week - c.cohort_week) / (86400 * 7))::INT AS weeks_since,
COUNT(DISTINCT c.customer_id) AS retained
FROM cohorts c
JOIN activity a USING (customer_id)
WHERE a.active_week >= c.cohort_week
GROUP BY 1, 2
)
SELECT
r.cohort_week, r.weeks_since,
r.retained, s.size,
r.retained * 1.0 / s.size AS retention_rate
FROM retained r
JOIN cohort_size s USING (cohort_week)
WHERE r.weeks_since IN (1, 2, 4, 8)
ORDER BY 1, 2;Edge case: a customer can be active multiple times in week N — COUNT(DISTINCT) handles that. The "weeks_since" calc here uses date arithmetic; some dialects need DATE_PART or AGE.
P3. Sessionize events into 30-minute idle-gap sessions
Given events(customer_id, event_at), return (customer_id, session_id, session_start, session_end, n_events) where a session ends if there's a gap > 30 minutes.
Show solution
WITH flagged AS (
SELECT
customer_id, event_at,
CASE
WHEN LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at) IS NULL
OR EXTRACT(EPOCH FROM event_at -
LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at)) / 60 > 30
THEN 1 ELSE 0
END AS is_new_session
FROM events
),
sessioned AS (
SELECT
customer_id, event_at,
SUM(is_new_session) OVER (PARTITION BY customer_id ORDER BY event_at) AS session_id
FROM flagged
)
SELECT
customer_id, session_id,
MIN(event_at) AS session_start,
MAX(event_at) AS session_end,
COUNT(*) AS n_events
FROM sessioned
GROUP BY 1, 2
ORDER BY 1, 2;P4. Funnel: signup → first session → repeat session
Of customers who signed up in the last 30 days: how many launched a first session within 7 days? Of those, how many launched a second session within 30 days of the first?
Show solution
WITH signup AS (
SELECT customer_id, MIN(signed_up_at) AS signup_at
FROM customers
WHERE signed_up_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
),
first_session AS (
SELECT s.customer_id, s.signup_at, MIN(g.started_at) AS first_at
FROM signup s
JOIN gpu_sessions g
ON g.customer_id = s.customer_id
AND g.started_at BETWEEN s.signup_at AND s.signup_at + INTERVAL '7 days'
GROUP BY 1, 2
),
second_session AS (
SELECT f.customer_id
FROM first_session f
JOIN gpu_sessions g
ON g.customer_id = f.customer_id
AND g.started_at > f.first_at
AND g.started_at <= f.first_at + INTERVAL '30 days'
GROUP BY 1
)
SELECT
(SELECT COUNT(*) FROM signup) AS signups,
(SELECT COUNT(*) FROM first_session) AS first_session_within_7d,
(SELECT COUNT(*) FROM second_session) AS second_session_within_30d;Senior touch: the time-window bounds go in the JOIN, not WHERE. Prevents the LEFT-JOIN-NULL trap if someone later switches to LEFT JOIN to count drop-offs.
P5. Longest consecutive-day usage streak per customer
For each customer, return the longest run of consecutive calendar days on which they had at least one session.
Show solution
WITH daily AS (
SELECT DISTINCT customer_id, DATE_TRUNC('day', started_at)::DATE AS d
FROM gpu_sessions
),
keyed AS (
SELECT
customer_id, d,
d - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY d))::INT AS run_key
FROM daily
),
runs AS (
SELECT customer_id, run_key, COUNT(*) AS streak_days
FROM keyed GROUP BY 1, 2
)
SELECT customer_id, MAX(streak_days) AS longest_streak
FROM runs
GROUP BY 1
ORDER BY longest_streak DESC;B · Debugging problems
P6. "Customers with no sessions" returns nothing
Intent: list customers who've never had a GPU session. The query returns zero rows even though we know such customers exist. Fix it.
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM gpu_sessions);Show fix & reasoning
Bug: if gpu_sessions.customer_id contains any NULL, NOT IN returns zero rows for every customer (because x NOT IN (..., NULL) evaluates to UNKNOWN).
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM gpu_sessions g WHERE g.customer_id = c.customer_id
);Alternative: LEFT JOIN ... WHERE g.customer_id IS NULL. Both are correct; NOT EXISTS is the most defensible against NULLs.
P7. Customer revenue is doubled
Intent: total revenue per customer with their region (regions are stored in a separate dim table, one row per customer). The query produces revenue values 2× what they should be for some customers. Why?
SELECT c.customer_id, r.region, SUM(g.revenue) AS total
FROM customers c
JOIN customer_region_history r ON r.customer_id = c.customer_id
LEFT JOIN gpu_sessions g ON g.customer_id = c.customer_id
GROUP BY 1, 2;Show fix & reasoning
Bug: the table is customer_region_history, not customer_region — it has multiple rows per customer (one per region change). Joining it duplicates sessions, so SUM(revenue) doubles for customers with 2 region rows.
WITH current_region AS (
SELECT DISTINCT ON (customer_id)
customer_id, region
FROM customer_region_history
ORDER BY customer_id, changed_at DESC
),
customer_revenue AS (
SELECT customer_id, SUM(revenue) AS total
FROM gpu_sessions GROUP BY 1
)
SELECT c.customer_id, r.region, COALESCE(cr.total, 0) AS total
FROM customers c
LEFT JOIN current_region r ON r.customer_id = c.customer_id
LEFT JOIN customer_revenue cr ON cr.customer_id = c.customer_id;The senior diagnostic: "Let me check the cardinality of customer_region_history. If it's not 1-per-customer, I have a fanout."
P8. Latest plan per customer returns nulls inconsistently
Intent: latest plan per customer. The query returns NULL for the plan column on most rows. Fix it.
SELECT
customer_id,
LAST_VALUE(plan) OVER (PARTITION BY customer_id ORDER BY changed_at) AS latest_plan
FROM plan_changes;Show fix & reasoning
Bug: the default window frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. LAST_VALUE over that frame returns the current row, not the partition's last.
SELECT
customer_id,
LAST_VALUE(plan) OVER (
PARTITION BY customer_id
ORDER BY changed_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS latest_plan
FROM plan_changes;
-- Cleaner alternative: filter to the most recent row
SELECT customer_id, plan FROM (
SELECT customer_id, plan,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) AS rn
FROM plan_changes
) WHERE rn = 1;P9. Daily DAU query suddenly slow
This query used to run in 2 seconds; now it takes 90. Same data volume, same shape. What's likely wrong and how do you investigate?
SELECT
DATE(started_at) AS day,
COUNT(DISTINCT customer_id) AS dau
FROM gpu_sessions
WHERE DATE(started_at) >= CURRENT_DATE - 7
GROUP BY 1;Show diagnosis
Diagnosis: the DATE(started_at) function call on the filtered column likely defeats partition pruning. If gpu_sessions is date-partitioned, the optimizer can't push the predicate down — it scans every partition.
SELECT
DATE_TRUNC('day', started_at)::DATE AS day,
COUNT(DISTINCT customer_id) AS dau
FROM gpu_sessions
WHERE started_at >= CURRENT_DATE - INTERVAL '7 days'
AND started_at < CURRENT_DATE + INTERVAL '1 day'
GROUP BY 1;Other suspects (mention all of these): stale statistics → ANALYZE; new skew on customer_id; an index dropped; warehouse compute downsized; concurrent heavy queries. The senior move is naming the checks in order: query plan first, then partition pruning, then stats, then skew.
P10. "Active users in the last 7 days" undercounts
Intent: customers who launched at least one session in the last 7 days. Stakeholders insist the number is too low. Find the bug.
SELECT COUNT(DISTINCT customer_id) AS active
FROM gpu_sessions
WHERE started_at BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE;Show fix & reasoning
Two bugs. (1) BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE excludes today's sessions after midnight — CURRENT_DATE is 2026-05-12 00:00:00. (2) On warehouses where started_at is UTC, "last 7 days" by user-local time is a separate question — sessions in user-Pacific Sunday land in UTC Monday.
SELECT COUNT(DISTINCT customer_id) AS active
FROM gpu_sessions
WHERE started_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
AND started_at < CURRENT_TIMESTAMP;The senior move is to ask "do you mean 7 calendar days, or the last 7×24 hours from right now?" before fixing — these can disagree by 5–15% depending on the day.
Drill protocol
Enable drill mode. Read each problem. Give yourself 12 minutes. Write the SQL on paper or in a notebook (not Postgres yet — the screen is whiteboard-flavored). Speak through your approach. Reveal the solution. Compare. Mark "practiced." Aim for 7/10 cleared before the screen, with at least 2 of the debugging ones.