SQL Interview Prep · Lesson

Day-N and Rolling Retention

The difference between classic, rolling, and bounded retention definitions.

Day-N and Rolling Retention is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Retention Has Multiple Definitions

An interviewer will rarely just say "compute retention". The sharp follow-up is: which retention? The same data yields very different numbers depending on the definition.

The three you must know: Day-N (classic) retention, rolling (unbounded) retention, and bounded-window retention. Knowing the difference, and asking which one the business wants, is itself the skill being tested.

Day-N (Classic) Retention

Day-N retention asks: was the user active on exactly day N after their first action? Day-1, Day-7, and Day-30 are the canonical mobile-app metrics.

The key word is exactly. A user active on day 6 and day 8 but not day 7 is not Day-7 retained under the classic definition. This precision is what makes the count strict and the curve jagged.

Computing the Day Difference

Day-N retention hinges on the number of days between the cohort start and each activity day. In Postgres, subtracting two dates yields an integer day count directly.

Other dialects: DATEDIFF(day, start, d) in SQL Server, DATEDIFF(d, start) in MySQL. State the dialect; the concept (a day offset) is identical.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
),
act AS (
  SELECT DISTINCT user_id, event_at::date AS day
  FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

A Day-7 Retention Query

To get Day-7 retention rate: count distinct users whose day offset equals 7, divided by the cohort size. Use conditional aggregation so both numerator and denominator come from one scan.

The = 7 equality (not >= 7) is the classic-retention signature. Swapping in an inequality silently changes the definition.

WITH dn AS (
  SELECT c.user_id, (a.day - c.day0) AS day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
  COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
  COUNT(DISTINCT user_id)                               AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
        / NULLIF(COUNT(DISTINCT user_id), 0), 1)         AS d7_pct
FROM dn;

Rolling (Unbounded) Retention

Rolling retention at day N asks a gentler question: was the user active on day N or any day after? It credits a user as retained at day 7 if they returned on day 7, day 20, or ever later.

This produces a smoother, higher curve and is often preferred for measuring long-term stickiness. The signature change is from = N to >= N on the max activity day.

Rolling Retention With MAX Day

The clean way to compute rolling retention: find each user's last active day offset (MAX), then a user is rolling-retained at day N if that maximum is >= N.

One user equals one row after the MAX, so counting is simple. This also makes it obvious that rolling retention is monotonic: if you are retained at day 30 you are retained at every smaller N.

WITH last_day AS (
  SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
  GROUP BY c.user_id
)
SELECT
  COUNT(*)                                          AS cohort_size,
  COUNT(*) FILTER (WHERE max_day_n >= 7)            AS rolling_d7,
  ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
        / NULLIF(COUNT(*), 0), 1)                    AS rolling_d7_pct
FROM last_day;

Bounded-Window Retention

The middle ground is bounded retention: active at least once within a window around day N, say days 5 through 9 for a "week 1" metric. It tolerates users who are not active on the exact day but smooths less than fully rolling.

This is the most business-realistic definition because real usage is bursty. The query uses a BETWEEN on the day offset.

SELECT
  COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
                      THEN user_id END) AS week1_retained,
  COUNT(DISTINCT user_id)               AS cohort_size
FROM dn;

Three Definitions, Same User

Make the difference concrete. A user starts day 0, then is active only on day 9.

Classic Day-7: NOT retained (no activity on day 7 exactly).
Rolling Day-7: retained (max day 9 >= 7).
Bounded 5–9 week-1: retained (day 9 falls in the window).

Same data, three answers. In an interview, narrate one example like this to prove you understand the semantics, not just the syntax.

Period Granularity: Day vs Week vs Month

"Day-N" generalizes to period-N. For a B2B product with monthly usage, day-level retention is noise; you would bucket by month and ask about month-N. The mechanics are identical, only the truncation grain changes.

Pick the grain to match the product's natural usage cadence, and say so. Daily for consumer mobile, weekly or monthly for slower B2B tools.

-- weekly grain: offset in whole weeks
SELECT
  c.user_id,
  FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

The Survivorship and Maturity Trap

A subtle senior probe: do not report Day-30 retention for a cohort that is only 10 days old. They have not had the chance to be day-30 active, so their value is artificially 0, not truly low retention.

Guard by only including cohorts whose age >= N when reporting Day-N. Filter on CURRENT_DATE - day0 >= N. Forgetting this makes recent cohorts look catastrophically bad.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30;  -- mature enough for Day-30

Choosing a Definition Out Loud

The best interview answer is not a query, it is a question back: "Do you want classic Day-N, rolling, or a bounded window? And what is the natural period?"

Then state the trade-off: classic is strict and good for exact-day product hooks; rolling overstates short-term but captures lifetime stickiness; bounded is the realistic compromise. Demonstrating you choose the metric on purpose is the whole point of the lesson.

Quick Check

A user's first action is day 0; their only other activity is on day 12. Under each definition, are they retained at Day-7?

Recap: Retention Definitions

Day-N and rolling retention takeaways:

Classic Day-N: active on exactly day N (offset = N) — strict, jagged curve.
Rolling: active on day N or later (MAX offset >= N) — smoother, monotonic, measures stickiness.
Bounded: active within a window (BETWEEN) — the realistic compromise.
Generalize day to any period grain matching the product's cadence.
Only report Day-N for mature cohorts (age >= N) to avoid the survivorship trap, and always ask which definition the business means.

Frequently asked questions

Is the “Day-N and Rolling Retention” lesson free?

Yes — the full text of “Day-N and Rolling Retention” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Day-N and Rolling Retention”?

The difference between classic, rolling, and bounded retention definitions. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Day-N and Rolling Retention” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep