SQL Retention Queries: Complete Cohorts and Return Windows

A retention window is part of the metric

Exact day-7 retention, any return during days 1–7, and activity in the next calendar month answer different questions. All three need enough observed time before a cohort can be compared fairly. This tutorial supplies complete PostgreSQL 16 queries, fictional inputs, and exact outputs. Each query includes its own maturity rule and does not modify stored data.

Distinguish calendar dates from elapsed time

Here signup defines a cohort, app activity defines a return, and the business reporting timezone is UTC. Inputs have already been converted to UTC calendar dates. as_of is an exclusive date boundary: October 17 means all dates through October 16 are available. A signup on October 9 has a complete day 7 on October 16. A signup on October 10 does not: its day 7 is October 17, which has not finished at the cutoff.

Specify the denominator and observation contract

The users input has one authoritative signup date per user. A real event stream needs identity resolution and a first-signup rule before this query. Null or invalid signup dates need an explicit rejection or quarantine policy. The event feed must also be complete through the cutoff; a calendar date becoming mature does not guarantee that delayed data has arrived.

Three definitions used below

Exact day 7: at least one activity on cohort_date + 7. Days 1–7: at least one activity after signup date and through cohort_date + 7. Calendar month 1: activity in the month following the signup month. Month 1 is not 30 days after signup. In all cases each eligible user contributes at most once to the numerator.

Count users even when they have no activity

The denominator comes from eligible users, not an inner join to activity. EXISTS expresses a Boolean return and ignores repeated activity rows. The fixture includes duplicate returns, a pre-signup event, a mature user with no returns, and an immature cohort. This makes the edge cases visible without needing a large dataset.

Exact day-7 retention

WITH params(as_of) AS (VALUES (DATE '2026-10-17')),
users(user_id,cohort_date) AS (
 VALUES (1,DATE '2026-10-01'),(2,DATE '2026-10-01'),
        (3,DATE '2026-10-09'),(4,DATE '2026-10-10')
), activity(user_id,activity_date) AS (
 VALUES (1,DATE '2026-10-08'),(1,DATE '2026-10-08'),
        (2,DATE '2026-10-02'),(2,DATE '2026-09-30'),
        (4,DATE '2026-10-17')
), eligible AS (
 SELECT u.* FROM users u CROSS JOIN params p
 WHERE u.cohort_date + 8 <= p.as_of
), flags AS (
 SELECT u.*,EXISTS (
   SELECT 1 FROM activity a CROSS JOIN params p
   WHERE a.user_id=u.user_id AND a.activity_date < p.as_of
     AND a.activity_date = u.cohort_date + 7
 ) AS retained FROM eligible u
)
SELECT cohort_date,COUNT(*) AS cohort_users,
       COUNT(*) FILTER (WHERE retained) AS retained_users,
       ROUND(100.0*COUNT(*) FILTER (WHERE retained)/COUNT(*),2) AS retention_pct
FROM flags GROUP BY cohort_date ORDER BY cohort_date;

cohort_date | cohort_users | retained_users | retention_pct
2026-10-01 | 2 | 1 | 50.00
2026-10-09 | 1 | 0 | 0.00

Two copies of user 1’s October 8 event count once. User 2 returned on day 1, not day 7. User 3 remains in the denominator with zero retention. User 4 is excluded with their entire cohort, even though the fixture contains an event on the unobserved date.

Any return during days 1–7

WITH params(as_of) AS (VALUES (DATE '2026-10-17')),
users(user_id,cohort_date) AS (
 VALUES (1,DATE '2026-10-01'),(2,DATE '2026-10-01'),
        (3,DATE '2026-10-09'),(4,DATE '2026-10-10')
), activity(user_id,activity_date) AS (
 VALUES (1,DATE '2026-10-08'),(1,DATE '2026-10-08'),
        (2,DATE '2026-10-02'),(2,DATE '2026-09-30'),
        (4,DATE '2026-10-17')
), eligible AS (
 SELECT u.* FROM users u CROSS JOIN params p
 WHERE u.cohort_date + 8 <= p.as_of
), flags AS (
 SELECT u.*,EXISTS (
   SELECT 1 FROM activity a CROSS JOIN params p
   WHERE a.user_id=u.user_id AND a.activity_date < p.as_of
     AND a.activity_date >= u.cohort_date + 1
     AND a.activity_date <= u.cohort_date + 7
 ) AS retained FROM eligible u
)
SELECT cohort_date,COUNT(*) AS cohort_users,
       COUNT(*) FILTER (WHERE retained) AS retained_users,
       ROUND(100.0*COUNT(*) FILTER (WHERE retained)/COUNT(*),2) AS retention_pct
FROM flags GROUP BY cohort_date ORDER BY cohort_date;

cohort_date | cohort_users | retained_users | retention_pct
2026-10-01 | 2 | 2 | 100.00
2026-10-09 | 1 | 0 | 0.00

The same eligible population now has a different numerator because user 2’s day-1 return qualifies. Their pre-signup event still does not. Both daily definitions require day 7 to finish, so their maturity condition is the same. If the requested window were 168 elapsed hours from a timestamp, use a timestamp-based query instead of these calendar dates.

Calendar month-1 retention with zero rows retained

WITH params(as_of) AS (VALUES (DATE '2026-10-17')),
users(user_id,signup_date) AS (
 VALUES (1,DATE '2026-07-31'),(2,DATE '2026-07-01'),
        (3,DATE '2026-08-15'),(4,DATE '2026-09-01')
), activity(user_id,activity_date) AS (
 VALUES (1,DATE '2026-08-01'),(1,DATE '2026-08-02'),
        (3,DATE '2026-10-01'),(4,DATE '2026-10-02')
), cohorts AS (
 SELECT user_id,DATE_TRUNC('month',signup_date)::date AS cohort_month
 FROM users
), flags AS (
 SELECT c.*,EXISTS (
   SELECT 1 FROM activity a
   WHERE a.user_id=c.user_id
     AND a.activity_date >= c.cohort_month + INTERVAL '1 month'
     AND a.activity_date < c.cohort_month + INTERVAL '2 months'
 ) AS retained
 FROM cohorts c CROSS JOIN params p
 WHERE c.cohort_month + INTERVAL '2 months' <= p.as_of
)
SELECT cohort_month,1 AS month_number,COUNT(*) AS cohort_users,
       COUNT(*) FILTER (WHERE retained) AS retained_users,
       ROUND(100.0*COUNT(*) FILTER (WHERE retained)/COUNT(*),2) AS retention_pct
FROM flags GROUP BY cohort_month ORDER BY cohort_month;

cohort_month | month_number | cohort_users | retained_users | retention_pct
2026-07-01 | 1 | 2 | 1 | 50.00
2026-08-01 | 1 | 1 | 0 | 0.00

July cohorts are measured in August; August cohorts in September. A July 31 signup can qualify with an August 1 event because this is a calendar-month metric. The September cohort would be measured over all of October, so it is excluded at October 17. The August cohort is shown with zero rather than disappearing because its user returns only in October.

Keep maturity inside every copyable query

For an exclusive daily cutoff, cohort_date + 8 <= as_of completes the whole day-7 date. A common <= current_date - 7 rule can admit today’s unfinished day-7 window. For calendar month 1, cohort-month start plus two months must be no later than the cutoff. Use a fixed supplied cutoff or trusted watermark in scheduled reporting so reruns have a documented observation boundary.

Explain one user before explaining the percentage

State which date assigns the user to a cohort, which return date qualifies, and when that return window ends. Then show why the user is included or excluded as of the cutoff. This makes disagreement about a metric easier to resolve than debating a final percentage.

Tests that expose an incorrect result

Move as_of back one day: the October 9 daily cohort must disappear. Duplicate an activity event: counts must stay unchanged. Remove all activity: mature cohorts must remain with zero. Add a return at the excluded cutoff: it must not affect a mature-window report. For a full multi-month matrix, generate the eligible cohort-period combinations first so zero-return periods are represented explicitly.

References and adjacent practice

PostgreSQL documents date and interval operations and EXISTS. For elapsed conversion windows, see defining a seven-day conversion metric.

FAQ

Is exact day 7 the same as days 1–7?

No. The first checks one date; the second checks a range of seven dates. Report their definitions alongside the labels.

Do I always need a date spine?

Not for a single return window per cohort, as shown here. A dense cohort-period matrix needs a complete set of eligible periods to display zeros.

How do repeated events affect retention?

They do not increase a user-level numerator. EXISTS checks whether at least one qualifying event exists.