The Slow Fade

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

iHeartMedia

Problem

We review signup retention one calendar year at a time, so each cohort's curve ends in December of the year its users registered. Put each user in users into a cohort by the calendar month they registered, then for each month from signup through that December report the decimal fraction of the whole cohort with at least one session in user_sessions that month, labeled by how many months after signup it falls and leaving out any month in which nobody from the cohort came back. Walk from the oldest cohort to the newest, each curve running from the signup month forward.

They all arrive together. Watch the room empty, month by month.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

What this really is

Strip off the growth-metric costume and this is a per-person membership count inside a fenced window. Each cohort's curve lives only inside the calendar year its users signed up in, and the rate is unique active users divided by the whole cohort. Anyone can bucket by month. What separates candidates is the fence and the count. Subtract month digits without pinning each session to the cohort's own year, and the seed's '2025-02' cohort quietly absorbs its 2026 sessions as months 0 through 7. Count session rows instead of people, and '2024-07' reads 0.625 at month 1 when only three of its eight users came back.

Why the year has to match first

Inside one calendar year, months since signup is just the month digits subtracted: substr(s.session_start, 6, 2) - substr(c.cohort_month, 6, 2). That shortcut is only honest because the year is pinned before the arithmetic runs. Compare substr(s.session_start, 1, 4) to substr(c.cohort_month, 1, 4) as text, and every session from another year drops out. Leave the comparison off, and a session two Decembers later lands at a perfectly plausible offset that nobody will question on a dashboard.

Break down the requirements

Step 1: Bucket signups by year-month

substr(signup_date, 1, 7) gives each user a cohort_month of 'YYYY-MM'. Taking the prefix rather than the raw value matters on this seed: a couple of signup_date values carry a time of day, and the prefix files them with everyone else from that month.

Step 2: Pin the denominator to the whole cohort

cohort_sizes is its own CTE because the base is everyone who signed up that month, COUNT(*) over cohorts, never the users active in some later month. Compute it once, and every (cohort_month, months_since_signup) cell divides by the same number.

Step 3: Fence each session into the signup year

Join cohorts to user_sessions on user_id and keep a session only when its year prefix equals the cohort's. Then the month digits alone give months_since_signup, and the outer WHERE st.months_since_signup >= 0 drops activity from earlier in that year than the signup.

Step 4: Count people, then divide as a decimal

Aggregate with COUNT(DISTINCT st.user_id) so several sessions in one month collapse to one retained user. Then CAST that count to a float before dividing by cs.cohort_size, or SQLite hands you integer division and a wall of zeros.

The solution

WITH cohorts AS (
  SELECT user_id, substr(signup_date, 1, 7) AS cohort_month
  FROM users
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
),
sessions_tagged AS (
  SELECT
    c.cohort_month,
    s.user_id,
    substr(s.session_start, 6, 2) - substr(c.cohort_month, 6, 2) AS months_since_signup
  FROM cohorts c
  JOIN user_sessions s ON c.user_id = s.user_id
  WHERE substr(s.session_start, 1, 4) = substr(c.cohort_month, 1, 4)
)
SELECT
  st.cohort_month,
  st.months_since_signup,
  CAST(COUNT(DISTINCT st.user_id) AS DOUBLE) / cs.cohort_size AS retention_rate
FROM sessions_tagged st
JOIN cohort_sizes cs ON st.cohort_month = cs.cohort_month
WHERE st.months_since_signup >= 0
GROUP BY st.cohort_month, st.months_since_signup, cs.cohort_size
ORDER BY st.cohort_month, st.months_since_signup

Common follow-up questions

• How would you let each curve run for its first 12 months, even across a New Year? (Tests replacing the same-year fence with a year-aware offset, `(session_year - cohort_year) * 12` plus the month difference, capped at 11.)
• How would you show month 0 as an explicit row for every cohort, even when nobody in it had a signup-month session? (Tests building a cohort-by-offset spine and left-joining the active counts so empty cells read 0.)
• A user registers, goes quiet, then registers again months later. How does that distort the cohort? (Tests cohort-assignment thinking: the query pins each user to one `signup_date`, so reactivation needs its own cohort definition.)