Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Netflix
Build a month-relative cohort retention table showing what percentage of each signup cohort is still active N months later.
Netflix's growth team needs the one table that tells the truth about retention: a cohort grid where each row is a signup month (cohort — everyone who joined in January 2024, say), each column is months since signup (month 0, 1, 2, 3…), and each cell is the % of that cohort still active that many months later.
The build has three moves: assign every subscriber a cohort month from signup date, convert each active month into a month number (event month minus cohort month — the date arithmetic is the technical heart), then divide retained subscribers by cohort size per cell. Read down a column for onboarding health, across a row for a cohort's decay curve.
Interpretation rules matter more than SQL here. Month-one churn of 25–33% is the headline — fix onboarding before anything else. Young cohorts (March has one data point) are censored: describe their start, refuse to project their curve. And never quote a single blended retention % — the cohort grid exists precisely because blending lies.
Subscribers with subscriber_id, signup_date, plan_type, country; subscription_events recording one 'active_month' row per subscriber per calendar month they were active/billed, used to derive which months each subscriber was retained.
CREATE TABLE subscribers (
subscriber_id INTEGER PRIMARY KEY,
signup_date DATE,
plan_type TEXT,
country TEXT
);
CREATE TABLE subscription_events (
event_id INTEGER PRIMARY KEY,
subscriber_id INTEGER,
event_type TEXT, -- 'active_month' recorded once per calendar month the subscriber was billed/active
event_month DATE, -- first day of the month
FOREIGN KEY (subscriber_id) REFERENCES subscribers(subscriber_id)
);Your target
Build a month-relative cohort retention table showing what percentage of each signup cohort is still active N months later.
Loading the expected output…
WITH cohorts AS (
SELECT
subscriber_id,
strftime('%Y-%m-01', signup_date) AS cohort_month
FROM subscribers
),
activity AS (
SELECT
a.subscriber_id,
c.cohort_month,
a.event_month,
(CAST(strftime('%Y', a.event_month) AS INTEGER) - CAST(strftime('%Y', c.cohort_month) AS INTEGER)) * 12
+ (CAST(strftime('%m', a.event_month) AS INTEGER) - CAST(strftime('%m', c.cohort_month) AS INTEGER)) AS month_number
FROM subscription_events a
JOIN cohorts c ON c.subscriber_id = a.subscriber_id
WHERE a.event_type = 'active_month'
),
cohort_sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size
FROM cohorts
GROUP BY cohort_month
),
retention AS (
SELECT
cohort_month,
month_number,
COUNT(DISTINCT subscriber_id) AS retained_subscribers
FROM activity
GROUP BY cohort_month, month_number
)
SELECT
strftime('%Y-%m', r.cohort_month) AS cohort_month,
cs.cohort_size,
r.month_number,
r.retained_subscribers,
ROUND(100.0 * r.retained_subscribers / cs.cohort_size, 1) AS retention_rate_pct
FROM retention r
JOIN cohort_sizes cs ON cs.cohort_month = r.cohort_month
ORDER BY r.cohort_month, r.month_number;
Step-by-step Solution:
The cohorts CTE assigns each subscriber to a cohort_month based on the calendar month of their signup_date.
The activity CTE joins each active_month event back to the subscriber's cohort and computes month_number — the number of calendar months between the cohort month and that activity month — using DATE_PART on year and month rather than date subtraction, so it works cleanly across month boundaries.
cohort_sizes counts how many subscribers started in each cohort, which becomes the denominator.
The retention CTE counts distinct subscribers still active at each month_number per cohort.
The final SELECT joins retention counts back to cohort_sizes and calculates retention_rate_pct, producing the classic cohort-by-month-number retention triangle used in subscription analytics.
Computing the reference output…
The leak is month one: every cohort loses 25-33% of subscribers within 30 days, and the January cohort halves again by month two (50%) on its way to 25% by month three. Fix first-month onboarding and activation — later-month retention compounds from whoever survives month one.
What the interviewer asks next
“Every cohort loses 25–33% in month one — onboarding failure or wrong audience? How do you separate the two?”
Strong answer covers: Segment month-one churn by plan and country before prescribing: concentrated churn means product/expectation mismatch, diffuse churn means acquisition quality.
“The March cohort has a single data point — what can you claim about it, and what can't you?”
Strong answer covers: Censoring discipline: young cohorts can't speak to long-run retention; present March as 'starts like the rest' and refuse to extrapolate its curve.
“January sits at 25% by month three — sketch month six and defend your number.”
Strong answer covers: Extrapolation under uncertainty: flattening vs continued decay assumptions, confidence bands widening with time, and naming the one metric (month-1 save rate) that moves the whole curve most.
“February's month-one retention (66.7%) trails January's (75%) — worse onboarding, or n=3 versus n=4 noise?”
Strong answer covers: Significance humility at tiny n: one subscriber flips February by 33 points — report the gap with its fragility attached and demand bigger cohorts before declaring a trend.
“The seed carries plan (Premium/Standard/Basic) and country per subscriber, all unused. What single cut would you run next, and what would it decide?”
Strong answer covers: Next-best analysis: month-one churn by plan tier tests whether price or value perception drives early exits — the answer splits the fix between pricing/packaging and onboarding.
“Subscriber 10 (Basic, January) churned after month zero — the fastest exit in the data. What hypothesis does one row generate?”
Strong answer covers: Anecdote as hypothesis generator, not evidence: Basic-plan instant churn suggests trial-to-paid friction at the low end — worth a targeted exit survey, never a conclusion.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.