Calculate First-Week Retention by Signup Cohort

Compute First-Week Retention by Signup Week

You have these tables:

Table	Column	Meaning
users	user_id	User identifier.
users	signup_date	The user's signup date or timestamp.
users	plan_type	The user's plan type.
events	user_id	User associated with an event.
events	event_ts	Event timestamp.

Group users into cohorts by the calendar week containing signup_date. In PostgreSQL, a calendar week begins on Monday at midnight. Name that week-start value signup_week.

For this exercise, a user is retained if the user has at least one matching event in the half-open interval from signup_date through, but not including, signup_date plus seven days. An event exactly at signup qualifies; an event exactly seven days later does not. Match events to users by user_id.

Write one read-only PostgreSQL query that returns one row for each signup-week cohort present in users, with these columns in this order:

• signup_week: the cohort's week-start timestamp.
• cohort_size: the number of distinct user identifiers in that cohort.
• retained_users: the number of distinct users in that cohort meeting the first-week event condition.
• retention_rate: retained_users divided by cohort_size, rounded to four decimal places.

The denominator includes cohort users with no qualifying event. Multiple qualifying events must not count a retained user more than once. Include cohorts with zero retained users; do not add calendar weeks that have no users.

Order the result by signup_week ascending. If a NULL week-start group is present, place it last.

Compute First-Week Retention by Signup Week

Table users

column	type	constraints
user_id	INTEGER
signup_date	TIMESTAMP
plan_type	TEXT

Sample data:

plan_type	signup_date	user_id
free	2026-09-06 23:30:00	1
pro	2026-09-04 09:00:00	2
enterprise	2026-08-31 12:00:00	3
free	2026-09-07 00:00:00	4
pro	2026-09-09 23:30:00	5
NULL	2026-09-13 10:00:00	6
trial	2026-09-21 09:00:00	7
free	2026-09-23 12:00:00	8
pro	2026-09-25 08:00:00	9
enterprise	NULL	10
NULL	NULL	11

Table events

column	type	constraints
user_id	INTEGER
event_ts	TIMESTAMP

Sample data:

event_ts	user_id
2026-09-06 23:30:00	1
2026-08-31 11:59:59	3
2026-09-07 12:00:00	3
2026-09-07 00:00:00	4
2026-09-07 00:00:00	4
2026-09-14 00:00:00	4
2026-09-16 23:29:59	5
2026-09-10 12:00:00	5
2026-09-28 09:00:00	7
2026-09-23 11:59:59	8
NULL	9
2026-09-10 12:00:00	10
2026-09-10 12:00:00	99
2026-09-10 12:00:00	NULL

Expected output

signup_week	cohort_size	retained_users	retention_rate
2026-08-31 00:00:00	3	1	0.3333
2026-09-07 00:00:00	3	2	0.6667
2026-09-21 00:00:00	3	0	0
NULL	2	0	0