Approach: identify each user’s first event (cohort_date) and variant at that first event within the given week, count users per cohort (users_in_cohort), then count how many of those users had any event on cohort_date + 7 days (retained_day_7_count). Use CTEs and standard INTERVAL arithmetic.sql
WITH first_events AS (
-- pick each user's first event and the variant at that time, restricted to cohort window
SELECT
user_id,
MIN(occurred_at) AS cohort_date
FROM events
GROUP BY user_id
HAVING MIN(occurred_at) BETWEEN DATE '2025-01-01' AND DATE '2025-01-07'
),
first_variant AS (
-- get the variant associated with the user's first event (if multiple same-day events, pick earliest by event_type ordering or arbitrary)
SELECT
fe.user_id,
fe.cohort_date,
e.variant
FROM first_events fe
JOIN events e
ON e.user_id = fe.user_id
AND e.occurred_at = fe.cohort_date
-- If users have multiple events at same timestamp and different variants, this will duplicate rows;
-- use ROW_NUMBER() to deduplicate if needed. Example below:
),
first_variant_dedup AS (
SELECT
user_id,
cohort_date,
variant
FROM (
SELECT
fv.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY variant) AS rn
FROM first_variant fv
) t
WHERE rn = 1
),
cohort_sizes AS (
SELECT
cohort_date,
variant,
COUNT(DISTINCT user_id) AS users_in_cohort
FROM first_variant_dedup
GROUP BY cohort_date, variant
),
retained_day_7 AS (
-- find cohort users who had any event exactly 7 days after cohort_date
SELECT
fvd.cohort_date,
fvd.variant,
COUNT(DISTINCT fvd.user_id) AS retained_day_7_count
FROM first_variant_dedup fvd
JOIN events e
ON e.user_id = fvd.user_id
AND e.occurred_at = fvd.cohort_date + INTERVAL '7' DAY
GROUP BY fvd.cohort_date, fvd.variant
)
SELECT
c.cohort_date,
c.variant,
c.users_in_cohort,
COALESCE(r.retained_day_7_count, 0) AS retained_day_7_count,
ROUND( COALESCE(r.retained_day_7_count, 0) * 1.0 / c.users_in_cohort, 4) AS retention_rate
FROM cohort_sizes c
LEFT JOIN retained_day_7 r
ON r.cohort_date = c.cohort_date
AND r.variant = c.variant
ORDER BY c.cohort_date, c.variant;
