# The Slow Fade > They all arrive together. Watch the room empty, month by month. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Group users into signup cohorts by the month they registered, and track how each cohort holds up over the following months. For every month from signup onward, report the share of the cohort who had at least one session that month, labeled by how many months after signup it falls. ## Worked solution and explanation ### What this really is Strip off the growth-metric costume and this is a per-user set-membership count with one sharp edge: the denominator. Bucket each user by signup month, bucket each of their sessions by month, and the retention rate is nothing more than distinct actives divided by the size of the cohort. Anyone can write the numerator. Two things separate candidates. First, keeping the denominator the FULL cohort, computed once, instead of the count active in the prior period. Divide by the prior period and you have quietly switched to rolling retention, a different metric that hides churn. Second, remembering that integer division of two counts silently returns 0 for almost every cell, so your whole grid reads as zero retention. --- ### The month-difference trap The other place people slip is turning two 'YYYY-MM' strings into a month offset. Reach for a day-count hack (subtract Julian days, divide by 30) and you drift: February is 28 days, so a session exactly one calendar month later rounds down to month 0, and the error compounds across a year. Do plain calendar arithmetic instead: (active_year - cohort_year) * 12 + (active_month - cohort_month). Cast the four-digit year and two-digit month to integers first and it is exact at every year boundary, because you never compare the strings lexically. --- ### Break down the requirements #### Step 1: Bucket signups and sessions by month substr(signup_date, 1, 7) and substr(session_start, 1, 7) both give YYYY-MM. The cohorts CTE tags each user with a cohort_month. activity uses DISTINCT on (user_id, active_month) so a user with 50 sessions in March counts once for March, not fifty times. #### Step 2: Pre-compute the cohort size cohort_sizes is its own CTE because the denominator has to be the whole cohort, not the count of users active in some later month. Compute it once and every (cohort_month, months_since_signup) cell divides by the same number. #### Step 3: Join, offset, divide Join cohorts to activity on user_id, compute months_since_signup with calendar arithmetic, and keep only offsets >= 0 (a session cannot retain a user before they signed up). CAST the distinct active count to DOUBLE before dividing, or SQLite gives you integer division and a wall of zeros. --- ### The solution **Four CTEs build the retention grid** ```sql 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 ), activity AS ( SELECT DISTINCT user_id, substr(session_start, 1, 7) AS active_month FROM user_sessions ), active_cohorts AS ( SELECT c.cohort_month, a.user_id, (CAST(substr(a.active_month, 1, 4) AS INTEGER) - CAST(substr(c.cohort_month, 1, 4) AS INTEGER)) * 12 + (CAST(substr(a.active_month, 6, 2) AS INTEGER) - CAST(substr(c.cohort_month, 6, 2) AS INTEGER)) AS months_since_signup FROM cohorts c JOIN activity a ON c.user_id = a.user_id ) SELECT ac.cohort_month, ac.months_since_signup, CAST(COUNT(DISTINCT ac.user_id) AS DOUBLE) / cs.cohort_size AS retention_rate FROM active_cohorts ac JOIN cohort_sizes cs ON ac.cohort_month = cs.cohort_month WHERE ac.months_since_signup >= 0 GROUP BY ac.cohort_month, ac.months_since_signup ORDER BY ac.cohort_month, ac.months_since_signup ``` > **Common pitfall** > > Two silent zeros ruin this query. First, drop the CAST on the numerator and SQLite does integer division, returning 0 for every cell where actives are fewer than the cohort size, which is nearly all of them. Second, if you divide by the number active in the previous month instead of the full cohort, you have switched from classic retention to rolling retention without noticing. > **Interviewers watch for** > > Three tells of a strong answer. Did you make activity DISTINCT so a chatty user counts once per month rather than inflating retention? Is the denominator the full cohort size, computed in its own step, not a per-period count? And did you cast the numerator to a float before dividing? Miss any one and the grid looks plausible but is wrong. > **Cost analysis** > > users is 10M rows and user_sessions is far larger, so the DISTINCT that collapses sessions to one row per (user_id, month) is the dominant cost, hashing on that pair. Everything after is cheap: the join on user_id feeds a group that is at most a couple of years of month offsets per cohort, and cohort_sizes is a tiny grouped scan. --- ## Common follow-up questions - How would you cap the report at the first 12 months after signup? _(Tests whether the candidate filters on the derived months_since_signup and can discuss doing it in a HAVING (or an outer WHERE over the CTE) after the grouping.)_ - A user signs up, goes quiet, then registers again months later. How does that distort the cohort? _(Tests cohort-assignment thinking. The query pins each user to their signup_date, so a second registration is invisible. Reactivation cohorts need a different cohort definition.)_ - How would you show month 0 as an explicit baseline row for every cohort, even the empty ones? _(Tests whether the candidate can build a full cohort-by-month spine with a left join so month 0 always appears, even when no user in the cohort had a signup-month session.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_slow_fade) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.