0Pricing
SQL Interview Prep · Lesson

Building a Retention Matrix

Counting active users by cohort and period offset to form a retention table.

Building a Retention Matrix is a free SQL Interview Prep lesson on CoddyKit — lesson 2 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.

What a Retention Matrix Is

The follow-up to defining a cohort is the famous retention matrix: rows are cohorts, columns are period offsets (month 0, 1, 2, ...), and each cell counts how many of that cohort were still active at that offset.

Interviewers love this because it forces you to combine cohort assignment, a join back to activity, a period-difference calculation, and a pivot. It is the single most representative query of product analytics.

The Two Inputs

You need two things: each user's cohort period (from the previous lesson) and a record of every active period per user. Activity comes from the same events table, collapsed to the period grain.

So plan the query as: cohort CTE, then an activity CTE that lists which months each user was active, then join them.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Listing Active Periods

The activity CTE answers "in which months was each user active?" Truncate every event to the month and de-duplicate with DISTINCT or GROUP BY, so a user active 40 times in March yields one March row.

This per-user, per-month list is what you join against the cohort to measure survival across offsets.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Computing the Period Offset

The heart of the matrix is the period number: how many months after their cohort start was a given activity? Subtract cohort month from active month.

In Postgres a clean way is to count whole months between the two dates. A portable formula multiplies year difference by 12 and adds month difference; many engines also offer helpers. Offset 0 means the cohort's own starting month.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Joining Cohort to Activity

Join the cohort CTE to the activity CTE on user_id. Each output row says: this user, born in cohort X, was active in offset N. Counting distinct users per (cohort, offset) is the matrix in long form.

Because every cohort member is active in their own starting month, offset 0 should equal the cohort size, a built-in sanity check.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

The Long-Form Retention Table

Add the offset calculation and aggregate. You now have a tidy long-form result: one row per cohort per offset with a retained-user count. Many interviewers accept this directly because pivoting is cosmetic.

Note the offset expression appears in both SELECT and GROUP BY since it is computed, not a stored column.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Pivoting Into Wide Columns

To get the classic grid, pivot offsets into columns with conditional aggregation: a SUM of a CASE per offset. This portable pattern works in every dialect without special PIVOT syntax.

Each CASE emits 1 when the row's period_number matches that column, so the SUM counts retained users for that offset.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

From Counts to Retention Rates

Interviewers usually want percentages, not raw counts. Divide each offset's retained users by the cohort size (offset 0). Cast to a float or multiply by 1.0 to avoid integer division, the most common silent bug here.

The result is a retention curve: 100% at month 0, decaying toward a plateau. That plateau is the metric stakeholders actually care about.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

The Integer-Division Trap

A guaranteed interview gotcha: in most engines 120 / 500 equals 0, not 0.24, because both operands are integers. Retention percentages quietly come out as all zeros.

Fix it by making one side numeric: multiply by 100.0, CAST one operand to NUMERIC, or divide by NULLIF(size, 0) to also guard against an empty cohort. Saying "and NULLIF prevents divide-by-zero" earns bonus points.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Filling Missing Offsets With Zero

If a cohort had zero retained users at offset 2, the JOIN produces no row, leaving a hole in the matrix. To show an explicit 0, generate the full grid of (cohort, offset) combinations and LEFT JOIN the counts in.

Build the grid by CROSS JOINing cohorts with a numbers/offsets list, then coalesce the missing counts to zero. Interviewers appreciate that you noticed the gap.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Triangular Shape and Recency Bias

One more talking point: the matrix is triangular. A cohort that started last month cannot have a month-3 value yet, so later offsets have fewer cohorts contributing.

Comparing the average of a column across cohorts is therefore biased toward older cohorts. Mention that you would either show the triangle honestly or restrict comparisons to offsets every cohort has reached. This awareness separates analysts from query-writers.

Quick Check

Your retention query divides retained users by cohort size, but every percentage prints as 0 except month 0. What is the most likely cause?

Recap: The Retention Matrix

To build a retention matrix in an interview:

  • Assign each user a cohort period, then list each user's active periods de-duplicated.
  • Join them and compute the period offset (months between cohort and activity).
  • Aggregate to long form with COUNT(DISTINCT user_id); pivot via CASE if a grid is required.
  • Convert counts to rates carefully, avoiding integer division and divide-by-zero with 100.0 and NULLIF.
  • LEFT JOIN a generated grid to fill zero cells, and remember the matrix is triangular.

Frequently asked questions

Is the “Building a Retention Matrix” lesson free?

Yes — the full text of “Building a Retention Matrix” 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 “Building a Retention Matrix”?

Counting active users by cohort and period offset to form a retention table. 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 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Building a Retention Matrix” 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

  1. Defining a Cohort by First Action
  2. Building a Retention Matrix
  3. Day-N and Rolling Retention
  4. Churn and Resurrection Queries
← Back to SQL Interview Prep