Approach: normalize timestamps to dates/weeks, deduplicate events per user-day, and join cohorts (first activity week) to activity in subsequent windows.DAU (daily active users) — count distinct users with any event per day:sql
SELECT
DATE(timestamp) AS day,
COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY DATE(timestamp)
ORDER BY day;
sql
WITH first_activity AS (
SELECT user_id, DATE_TRUNC('week', MIN(timestamp))::date AS cohort_week
FROM events
GROUP BY user_id
),
user_activity AS (
SELECT
e.user_id,
DATE(e.timestamp) AS activity_date,
DATE_TRUNC('week', DATE(e.timestamp))::date AS activity_week
FROM events e
GROUP BY e.user_id, DATE(e.timestamp) -- dedupe multiple events same day
)
SELECT
f.cohort_week,
COUNT(DISTINCT f.user_id) AS cohort_size,
COUNT(DISTINCT CASE WHEN ua.activity_date = (f.cohort_week + INTERVAL '7 day')::date THEN ua.user_id END) AS retained_day_7,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN ua.activity_date = (f.cohort_week + INTERVAL '7 day')::date THEN ua.user_id END) / NULLIF(COUNT(DISTINCT f.user_id),0),2) AS retention_pct_day_7
FROM first_activity f
LEFT JOIN user_activity ua ON ua.user_id = f.user_id
GROUP BY f.cohort_week
ORDER BY f.cohort_week;
