SQL · Guides · October 29, 2026 · 11 min read

Cohort retention analysis in SQL: day-N, weekly matrix, rolling

Cohort retention answers one question: of the people who started in a given period, what share came back later? A cohort is a group of users who share a starting point (the day or week of their first visit, first purchase or sign-up). Retention is the share of the cohort that is active again N days or N weeks later. Laid out with cohorts as rows and periods as columns, it becomes the familiar triangular retention matrix.

In SQL, every retention query has the same three steps:

1. reduce the activity log to one row per user per day;
2. find each user’s first day (the cohort);
3. join the two and count, for each cohort, how many users were active at each offset.

The details are where the numbers go wrong: who counts as “new”, how integer division rounds, and what to show for periods that have not happened yet. This guide builds the analysis on the app log of a training airline (app_events, 1 July to 11 August 2026, snapshot at 18:00 on 11 August). Every query was run on that data and every table shows the real output, so you can reproduce each step in the Nomto SQL room.

Step 1: one row per user per day

The user key here is client_id, one installation of the app or one browser. The activity is “any event on that day”:

SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
FROM app_events;

DISTINCT matters twice: a user with twenty events on a day is one active day, and repeated deliveries of the same event (a known quirk of this log) disappear as well.

Step 2: the cohort is the first active day

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
)
SELECT client_id, MIN(active_day) AS first_day
FROM client_days
GROUP BY client_id;

This is “first” only within the data you have. The log begins on 1 July, so everyone active that day looks like a newcomer, including people who have used the app for years. The first days of any log are polluted this way, and they should not be used as cohorts. In this dataset the daily inflow of “new” clients only settles after the first week, so the examples below start their cohorts from mid-July.

If you have a real sign-up or install date, use it instead. Here the loyalty programme has joined_at, but many members join at an airport desk or on the website and never open the app, so for app retention the first event is the better starting point.

Day-N retention: D1 and D7

Classic day-N retention asks: was the user active exactly on day N after their first day? To compute it, join every active day back to the user and set a 0/1 flag per user:

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
),
first_days AS (
  SELECT client_id, MIN(active_day) AS first_day
  FROM client_days
  GROUP BY client_id
),
flags AS (
  SELECT f.client_id, f.first_day,
    MAX(CASE WHEN cd.active_day = f.first_day + 1 THEN 1 ELSE 0 END) AS d1,
    MAX(CASE WHEN cd.active_day = f.first_day + 7 THEN 1 ELSE 0 END) AS d7,
    MAX(CASE WHEN cd.active_day BETWEEN f.first_day + 1 AND f.first_day + 7
             THEN 1 ELSE 0 END) AS within7
  FROM first_days f
  JOIN client_days cd ON cd.client_id = f.client_id
  GROUP BY f.client_id, f.first_day
)
SELECT first_day, COUNT(*) AS clients,
  ROUND(100.0 * SUM(d1) / COUNT(*), 1) AS d1_pct,
  CASE WHEN first_day + 7 <= DATE '2026-08-10'
       THEN ROUND(100.0 * SUM(d7) / COUNT(*), 1) END AS d7_pct,
  CASE WHEN first_day + 7 <= DATE '2026-08-10'
       THEN ROUND(100.0 * SUM(within7) / COUNT(*), 1) END AS within7_pct
FROM flags
WHERE first_day BETWEEN '2026-07-28' AND '2026-08-04'
GROUP BY first_day
ORDER BY first_day;

Several details in this query are deliberate.

MAX(CASE ...) per user. After the join, each user has one row per active day. The MAX of a 0/1 flag is 1 if the condition held on any of those rows. Then SUM(flag) / COUNT(*) over users is the cohort’s share.

100.0, not 100. In PostgreSQL, SUM of integers divided by COUNT(*) is integer division and silently drops the fraction. Starting with 100.0 makes the whole expression numeric.

NULL for immature cells. The last complete day in the data is 10 August (11 August is cut at 18:00). A client who first appeared on 4 August would have their day 7 on 11 August, which is not complete. Their D7 flag would be 0 not because they did not return, but because the day has not been fully observed. A CASE without ELSE returns NULL for those cohorts, which is the honest value; a zero would look like a collapse in retention.

Three definitions, three different numbers. D1 is around 7–9%, D7 below 2%, yet about 18–20% of each cohort came back at least once within the first week (within7). None of them is wrong; they measure different things. Exact-day retention is strict and noisy for products people do not use daily, such as booking flights. “Returned within N days” (sometimes called bracket or unbounded retention, with a fixed horizon) is often more useful for them. Always state which definition a number uses.

Rolling retention needs a fixed horizon

Rolling retention counts users who came back on day N or later. Without an upper bound it depends on how much time has passed since the cohort started: an early cohort has had weeks to come back, a recent one only days. It then looks as if newer cohorts retain worse, when they are simply younger. The within7 column above avoids this by fixing the horizon at seven days and only showing cohorts for which all seven days are complete.

The weekly retention matrix

For a dashboard, weekly cohorts are easier to read than daily ones. The cohort is the calendar week of the first day; the column is how many calendar weeks later the user was active:

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
),
first_days AS (
  SELECT client_id, MIN(active_day) AS first_day
  FROM client_days
  GROUP BY client_id
),
weeks AS (
  SELECT f.client_id,
    CAST(date_trunc('week', f.first_day) AS DATE) AS cohort_week,
    CAST((CAST(date_trunc('week', cd.active_day) AS DATE)
        - CAST(date_trunc('week', f.first_day) AS DATE)) / 7 AS INTEGER) AS week_n
  FROM first_days f
  JOIN client_days cd ON cd.client_id = f.client_id
),
cells AS (
  SELECT cohort_week, week_n, COUNT(DISTINCT client_id) AS active
  FROM weeks
  GROUP BY cohort_week, week_n
),
pct AS (
  SELECT c.cohort_week, c.week_n, s.active AS clients,
    CASE WHEN c.cohort_week + 7 * c.week_n + 6 <= DATE '2026-08-09'
         THEN ROUND(100.0 * c.active / s.active, 1) END AS retention
  FROM cells c
  JOIN cells s ON s.cohort_week = c.cohort_week AND s.week_n = 0
)
SELECT cohort_week, clients,
  MAX(CASE WHEN week_n = 1 THEN retention END) AS w1,
  MAX(CASE WHEN week_n = 2 THEN retention END) AS w2,
  MAX(CASE WHEN week_n = 3 THEN retention END) AS w3,
  MAX(CASE WHEN week_n = 4 THEN retention END) AS w4
FROM pct
WHERE cohort_week BETWEEN DATE '2026-07-13' AND DATE '2026-08-03'
GROUP BY cohort_week, clients
ORDER BY cohort_week;

How the pieces fit:

• date_trunc('week', ...) returns the Monday of the week. The difference between two Mondays divided by 7 is the week number: 0 for the cohort’s own week, 1 for the next, and so on.
• The cohort size is the number of clients active in week 0, which is every client of the cohort by definition. Joining cells to itself on week_n = 0 puts that size next to each cell.
• The pivot from long format (cohort, week, value) to columns is conditional aggregation: MAX(CASE WHEN week_n = 1 THEN retention END).
• The maturity rule: week N of a cohort ends on cohort_week + 7 * N + 6. Only weeks that ended by Sunday 9 August, the last complete week, get a value. Without this rule the 20 July cohort would show 3.3% in week 3, which is just the two observed days of the week of 10 August, not a real drop.

Read the matrix along rows (how one cohort decays) and down columns (whether week-1 retention is improving or worsening from cohort to cohort). Here week 1 slips slightly from 13.2% to 11.9%; with three data points that is something to watch, not a conclusion. Weekly retention is also much higher than D7 because “any day of week 1” is a far wider window than “exactly day 7”.

Retention on purchases instead of visits

Retention does not have to mean opening the app. For an airline, “booked again” is closer to the money. Loyalty members are the one place where repeat customers are identifiable (ticket_members links tickets to member_id), so the cohort is the week of a member’s first booking and the event is another booking within 14 days:

WITH member_bookings AS (
  SELECT DISTINCT tm.member_id, b.book_ref, b.book_date
  FROM ticket_members tm
  JOIN tickets t ON t.ticket_no = tm.ticket_no
  JOIN bookings b ON b.book_ref = t.book_ref
),
firsts AS (
  SELECT member_id, MIN(book_date) AS first_booking
  FROM member_bookings
  GROUP BY member_id
),
flags AS (
  SELECT f.member_id,
    CAST(date_trunc('week', f.first_booking) AS DATE) AS cohort_week,
    MAX(CASE WHEN mb.book_date > f.first_booking
              AND mb.book_date <= f.first_booking + INTERVAL '14 days'
             THEN 1 ELSE 0 END) AS rebooked_14d
  FROM firsts f
  JOIN member_bookings mb ON mb.member_id = f.member_id
  GROUP BY f.member_id, f.first_booking
)
SELECT cohort_week, COUNT(*) AS members,
  ROUND(100.0 * SUM(rebooked_14d) / COUNT(*), 1) AS rebooked_14d_pct
FROM flags
WHERE cohort_week BETWEEN DATE '2026-06-29' AND DATE '2026-07-20'
GROUP BY cohort_week
ORDER BY cohort_week;

The DISTINCT in the first CTE matters: a booking with three member tickets would otherwise appear three times. The last cohort shown is the week of 20 July, because 14 days after its last first booking (26 July) is 9 August, still before the snapshot.

The 29 June cohort stands out at 27.1%, and the reason is the same as with the app log: bookings start on 17 June, so the earliest “first bookings” include frequent flyers who had booked before the data begins. Frequent flyers rebook more often, which inflates the early cohort. Before you celebrate or worry about one cohort, check whether it is an edge of the data.

Common mistakes in cohort retention SQL

• Using the first days of the log as cohorts. Everyone looks new there. Skip them or use a true sign-up date.
• Counting events instead of users. Retention is a share of users; reduce to user-day first and count distinct users.
• Integer division. 100 * SUM(flag) / COUNT(*) in PostgreSQL rounds down to a whole number. Use 100.0.
• Zeros for periods that have not happened yet. Immature cells must be NULL, or the newest cohorts look like a disaster.
• Rolling retention without a horizon. Older cohorts have had more time, so they always look better.
• Mixing definitions. D7, “within 7 days” and “week 1” are three different numbers; label which one you show.
• Devices are not people. A client_id is an installation or a browser cookie; one person with a phone and a laptop is two clients, and a cleared cookie creates a “new” one. Use a logged-in user ID when the question is about people.

All tables in this guide come from the Nomto SQL course dataset. In the SQL room you can change the horizon from 7 to 14 days, switch the cohort from week to day, or rebuild the matrix for one platform, and check the numbers yourself.

FAQ

What is cohort retention analysis?

It groups users by when they started (the cohort) and measures what share of each group is active again after N days or weeks. Comparing cohorts shows whether the product keeps new users better or worse over time.

How do I calculate day 7 retention in SQL?

Find each user’s first active day, join all their active days, flag users with activity on first_day + 7, and divide the number of flagged users by the cohort size. Show NULL for cohorts whose day 7 is not yet complete.

What is the difference between classic and rolling retention?

Classic (day-N) retention requires activity exactly on day N. Rolling retention counts anyone active on day N or later. Rolling is always higher and depends on how long you have observed the cohort, so fix an upper bound when you compare cohorts.

How do I build a retention matrix in SQL?

Compute the cohort week and the week offset for every user-day, count distinct users per (cohort, offset), divide by the size of offset 0, and pivot the offsets into columns with MAX(CASE WHEN week_n = k THEN ... END).

Why are my newest cohorts showing very low retention?

Usually because their later periods have not been fully observed yet. Add a maturity condition that returns NULL for any cell whose period ends after the last complete day in the data.

Also in 简体中文