Approach: define each user’s signup event (earliest "signup"), assign cohort_week (date_trunc('week', signup_time)), then for each event in next 0..4 weeks compute week_number relative to signup and count distinct active users. Join to cohort sizes to compute retention_rate. Assume timestamps are UTC unless noted; convert with AT TIME ZONE if business uses another timezone. If users re-signup, use first signup to assign cohort (alternatively, choose last signup — explain trade-offs).SQL (Postgres):sql
WITH first_signup AS (
-- find each user's first signup timestamp
SELECT
user_id,
MIN(occurred_at) AS signup_at
FROM events
WHERE event_type = 'signup'
GROUP BY user_id
),
cohort AS (
-- cohort week starts on Monday (ISO) in UTC; adjust with AT TIME ZONE if needed
SELECT
user_id,
signup_at,
date_trunc('week', signup_at AT TIME ZONE 'UTC')::date AS cohort_week
FROM first_signup
),
events_with_week AS (
-- consider all events (including signup) and compute week_number since signup
SELECT
e.user_id,
c.cohort_week,
c.signup_at,
e.occurred_at,
FLOOR(EXTRACT(EPOCH FROM (e.occurred_at AT TIME ZONE 'UTC' - c.signup_at AT TIME ZONE 'UTC')) / (7*24*60*60))::int AS week_number
FROM events e
JOIN cohort c USING (user_id)
),
cohort_activity AS (
-- count distinct active users per cohort_week and week_number for weeks 0..4
SELECT
cohort_week,
week_number,
COUNT(DISTINCT user_id) AS active_users
FROM events_with_week
WHERE week_number BETWEEN 0 AND 4
GROUP BY cohort_week, week_number
),
cohort_size AS (
SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_size
FROM cohort
GROUP BY cohort_week
)
SELECT
ca.cohort_week,
ca.week_number,
ca.active_users,
cs.cohort_size,
ROUND( (ca.active_users::numeric / cs.cohort_size) , 4) AS retention_rate
FROM cohort_activity ca
JOIN cohort_size cs USING (cohort_week)
ORDER BY cohort_week, week_number;
