Approach: - Assume timestamps are in UTC or convert to UTC for calendar calculations. - Deduplicate by user per day (one active user per calendar day). - First-touch = earliest occurred_at for a user. If multiple events at same earliest time, still counts as one first-touch. - Cohorts by calendar week (ISO week starting Monday) of first_touch. Retention at day N = percent of cohort users who had any event on cohort_first_date + N.1) DAU per UTC calendar day (dedup by user):sql
SELECT
(occurred_at AT TIME ZONE 'UTC')::date AS day_utc,
COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day_utc
ORDER BY day_utc;
sql
WITH first_touch AS (
SELECT
user_id,
MIN(occurred_at AT TIME ZONE 'UTC') AS first_at_utc
FROM events
GROUP BY user_id
),
cohort_users AS (
SELECT
user_id,
first_at_utc::date AS cohort_date,
to_char(first_at_utc::date, 'IYYY-IW') AS cohort_week -- ISO year-week
FROM first_touch
),
activity AS (
SELECT
user_id,
(occurred_at AT TIME ZONE 'UTC')::date AS activity_date
FROM events
),
cohort_activity AS (
SELECT
c.cohort_week,
c.cohort_date,
c.user_id,
a.activity_date,
(a.activity_date - c.cohort_date) AS days_from_cohort
FROM cohort_users c
LEFT JOIN activity a ON a.user_id = c.user_id
)
SELECT
cohort_week,
COUNT(DISTINCT user_id) AS cohort_size,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_from_cohort = 7 THEN user_id END) / NULLIF(COUNT(DISTINCT user_id),0), 2) AS pct_retained_day_7,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_from_cohort = 14 THEN user_id END) / NULLIF(COUNT(DISTINCT user_id),0), 2) AS pct_retained_day_14,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_from_cohort = 28 THEN user_id END) / NULLIF(COUNT(DISTINCT user_id),0), 2) AS pct_retained_day_28
FROM cohort_activity
GROUP BY cohort_week
ORDER BY cohort_week;
