SQL Window Functions for Cohort and Retention Analytics
Cohort retention answers a simple product question: after users first become active, how many return in later periods?
For example:
- Of users whose first activity was in January, what percentage was active in February?
- Do February cohorts retain better than January cohorts?
- Does retention drop immediately after signup, or gradually over several months?
SQL window functions are especially useful here because they let you calculate a user’s first activity date without collapsing the event-level data too early. From there, you can assign each activity event to a cohort and calculate retention periods.
This tutorial uses PostgreSQL syntax and monthly cohorts, but the same design applies to weekly or daily retention.
Define the Retention Metric Before Writing SQL
“Retention” is overloaded, so define it precisely.
This tutorial uses classic monthly activity retention:
- A user belongs to the month in which they first performed a qualifying event.
- A retained user is active at least once in a later calendar month.
- Period 0 is the cohort’s first month.
- Period 1 is the following month, period 2 is two months later, and so on.
- Retention rate is:
[
\text{retention rate} =
\frac{\text{active users from cohort in period N}}
{\text{users in original cohort}}
]
Suppose user u1 first appears in January and has events in January, February, and March:
| user_id | cohort_month | activity_month | retention_period |
|---|---|---|---|
| u1 | 2025-01-01 | 2025-01-01 | 0 |
| u1 | 2025-01-01 | 2025-02-01 | 1 |
| u1 | 2025-01-01 | 2025-03-01 | 2 |
A user can generate many events in a month, but they should count only once for retention. That is why the key analytical grain is:
One row per user, cohort month, and activity month.
This is different from:
- Rolling retention, such as “was the user active on or after day 30?”
- Subscription retention, where retention may mean an active paid subscription.
- Revenue retention, which compares recurring revenue rather than users.
The business definition determines the SQL.
Understand the Data Flow and Query Architecture
A reliable retention query is easiest to reason about as a small pipeline:
Raw event table
|
v
Filter qualifying events
|
v
Deduplicate to one row per user per activity month
|
v
Use a window function to find each user's first activity month
|
v
Calculate month offset from cohort month
|
v
Aggregate active users by cohort and period
|
v
Divide by original cohort size
Assume a source event table with at least:
| Column | Meaning |
|---|---|
user_id |
Stable identifier for a user |
event_ts |
Timestamp of the user action |
event_name |
Type of action, such as login, purchase, or workspace_created
|
In a production model, you would usually filter event_name to meaningful activity. For a collaboration product, a workspace_created or document_edited event may represent engagement better than a passive page_view.
The window function is the central piece:
MIN(activity_month) OVER (PARTITION BY user_id)
Unlike a normal GROUP BY, this returns the earliest month for each user while preserving one row for every user-month activity record.
Build a Runnable Monthly Cohort Query
The following PostgreSQL query includes a small event dataset and produces a complete retention table, including periods with zero retained users.
It assumes:
- The event history starts early enough to identify each user’s true first qualifying event.
- Events through April 2025 are complete.
- Any event in a month counts as activity.
- Cohorts are based on a user’s first observed active month.
WITH events AS (
SELECT *
FROM (
VALUES
('u1', DATE '2025-01-05'),
('u1', DATE '2025-01-20'),
('u1', DATE '2025-02-03'),
('u1', DATE '2025-03-01'),
('u2', DATE '2025-01-17'),
('u2', DATE '2025-02-10'),
('u3', DATE '2025-02-01'),
('u3', DATE '2025-02-28'),
('u3', DATE '2025-04-03'),
('u4', DATE '2025-02-15'),
('u4', DATE '2025-03-15'),
('u5', DATE '2025-03-10')
) AS t(user_id, event_ts)
),
-- One row per user per calendar month.
monthly_activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_ts)::date AS activity_month
FROM events
),
-- Assign each user to the month of their first observed activity.
tagged_activity AS (
SELECT
user_id,
activity_month,
MIN(activity_month) OVER (
PARTITION BY user_id
) AS cohort_month
FROM monthly_activity
),
-- Calculate the integer month offset from the cohort month.
user_periods AS (
SELECT DISTINCT
user_id,
cohort_month,
activity_month,
(
EXTRACT(YEAR FROM activity_month) * 12
+ EXTRACT(MONTH FROM activity_month)
- EXTRACT(YEAR FROM cohort_month) * 12
- EXTRACT(MONTH FROM cohort_month)
)::int AS retention_period
FROM tagged_activity
),
cohort_sizes AS (
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS cohort_size
FROM user_periods
WHERE retention_period = 0
GROUP BY cohort_month
),
-- Find the latest complete activity month represented in the source.
reporting_limit AS (
SELECT MAX(activity_month) AS max_activity_month
FROM tagged_activity
),
-- Generate period rows so zero-retention months are visible.
cohort_period_grid AS (
SELECT
cs.cohort_month,
cs.cohort_size,
series.retention_period
FROM cohort_sizes cs
CROSS JOIN reporting_limit rl
CROSS JOIN LATERAL GENERATE_SERIES(
0,
(
EXTRACT(YEAR FROM rl.max_activity_month) * 12
+ EXTRACT(MONTH FROM rl.max_activity_month)
- EXTRACT(YEAR FROM cs.cohort_month) * 12
- EXTRACT(MONTH FROM cs.cohort_month)
)::int
) AS series(retention_period)
)
SELECT
grid.cohort_month,
grid.retention_period,
grid.cohort_size,
COUNT(DISTINCT up.user_id) AS retained_users,
ROUND(
COUNT(DISTINCT up.user_id)::numeric / grid.cohort_size,
2
) AS retention_rate
FROM cohort_period_grid grid
LEFT JOIN user_periods up
ON up.cohort_month = grid.cohort_month
AND up.retention_period = grid.retention_period
GROUP BY
grid.cohort_month,
grid.retention_period,
grid.cohort_size
ORDER BY
grid.cohort_month,
grid.retention_period;
If you are still building confidence with SQL transformations, data models, and warehouse patterns around analyses like this, this data engineering roadmap can be a useful optional next step.
Interpret the Expected Results
The query returns the following retention table:
| cohort_month | retention_period | cohort_size | retained_users | retention_rate |
|---|---|---|---|---|
| 2025-01-01 | 0 | 2 | 2 | 1.00 |
| 2025-01-01 | 1 | 2 | 2 | 1.00 |
| 2025-01-01 | 2 | 2 | 1 | 0.50 |
| 2025-01-01 | 3 | 2 | 0 | 0.00 |
| 2025-02-01 | 0 | 2 | 2 | 1.00 |
| 2025-02-01 | 1 | 2 | 1 | 0.50 |
| 2025-02-01 | 2 | 2 | 1 | 0.50 |
| 2025-03-01 | 0 | 1 | 1 | 1.00 |
| 2025-03-01 | 1 | 1 | 0 | 0.00 |
For the January cohort:
-
u1andu2first appeared in January, so cohort size is 2. - Both were active in February, giving 100% period-1 retention.
- Only
u1was active in March, giving 50% period-2 retention. - Neither was active in April, giving 0% period-3 retention.
The generated period grid matters. Without it, a query typically omits January period 3 entirely because no matching activity rows exist. Missing rows can be mistaken for missing data rather than true zero retention.
Why Window Functions Are Better Than a Simple Join
You can calculate first activity with a grouped subquery:
SELECT user_id, MIN(activity_month)
FROM monthly_activity
GROUP BY user_id;
That is valid, but it produces one row per user. You then need to join the result back to the activity table to attach the cohort month to every activity row.
A window function avoids that extra logical step:
MIN(activity_month) OVER (PARTITION BY user_id) AS cohort_month
Each user-month row receives its cohort assignment directly.
Other useful window functions for retention-related analysis include:
-
LAG(activity_month)to calculate the gap between active months. -
LEAD(activity_month)to identify whether a later return occurred. -
ROW_NUMBER()to isolate a user’s first event when the exact first event, not just its month, matters. -
FIRST_VALUE(event_name)to identify a user’s first qualifying action, provided the window has an explicitORDER BY event_ts.
For example, to find the gap since a user’s previous active month:
SELECT
user_id,
activity_month,
LAG(activity_month) OVER (
PARTITION BY user_id
ORDER BY activity_month
) AS previous_activity_month
FROM monthly_activity;
This supports reactivation analysis, such as users returning after two or more inactive months.
Avoid Common Production Pitfalls
Treating duplicate events as retained users
Event logs are usually many-to-one: one user can create hundreds of events per month. Always deduplicate to user-month grain before counting, or use COUNT(DISTINCT user_id) at the final aggregation.
Pre-deduplicating is often cheaper and easier to validate.
Using incomplete current periods
If today is halfway through a month, the current month will naturally show lower activity than completed months. Do not compare it directly to full historical periods.
A common approach is to filter to the last fully completed month:
WHERE event_ts < DATE_TRUNC('month', CURRENT_DATE)
The correct cutoff depends on event-arrival delays and timezone conventions.
Misclassifying old users as new users
If your source table begins in 2025 but users existed in 2024, their “first observed event” is not their actual first event. This inflates newer cohorts with returning users.
Use a trusted user dimension with signup_at, or ensure the event history reaches far enough back for the business question.
Mixing timezones
DATE_TRUNC('month', event_ts) depends on the timestamp type and session timezone. If events are stored in UTC but business reporting uses America/New_York, convert explicitly before truncating.
In PostgreSQL, for a timestamptz column:
DATE_TRUNC(
'month',
event_ts AT TIME ZONE 'America/New_York'
)::date
Use one documented reporting timezone across all cohort calculations.
Allowing the denominator to change
Retention should generally use the original cohort size as its denominator. Do not divide period-2 active users by the number of users active in period 1 unless you intentionally want conditional retention.
Test the Model Like a Data Product
Retention models are compact, but small errors can change product decisions. Add tests at multiple levels.
Grain test: monthly_activity should have at most one row per (user_id, activity_month).
SELECT
user_id,
activity_month,
COUNT(*)
FROM monthly_activity
GROUP BY 1, 2
HAVING COUNT(*) > 1;
This should return zero rows.
Period-zero test: every cohort must have 100% retention in period 0.
SELECT *
FROM retention_output
WHERE retention_period = 0
AND retention_rate <> 1;
Numerator bounds test: retained users cannot exceed cohort size.
SELECT *
FROM retention_output
WHERE retained_users > cohort_size;
Known-user fixture test: keep a tiny fixture like the sample dataset in version control. Assert that January 2025 has a cohort size of 2 and period-2 retention of 0.50.
Freshness test: verify that the latest event timestamp is recent enough for the reporting date. A stale ingestion job can make retention appear to collapse.
Takeaway
Cohort retention becomes straightforward when you model it at user-month grain:
- Deduplicate events into active user periods.
- Use
MIN(...) OVER (PARTITION BY user_id)to assign a first-activity cohort. - Calculate each activity month’s offset from that cohort.
- Aggregate distinct active users by cohort and period.
- Join against a generated cohort-period grid to preserve zero-retention periods.
The window function assigns cohort membership without losing event context, while the final aggregation turns that user-level behavior into a retention matrix that product and business teams can interpret.
Top comments (0)