Approach: filter users who signed up in the last 90 days, count the cohort size, then count users who had at least one distinct 'login' event between day 1 and day 30 after their signup. Use DISTINCT to deduplicate multiple logins. Normalize timestamps to UTC (or a chosen timezone) to avoid DST/timezone issues.sql
WITH recent_signups AS (
SELECT
user_id,
signup_time AT TIME ZONE 'UTC' AS signup_utc
FROM users
WHERE signup_time >= (now() AT TIME ZONE 'UTC') - INTERVAL '90 days'
),
logins AS (
SELECT
pe.user_id,
pe.event_time AT TIME ZONE 'UTC' AS event_utc
FROM product_events pe
WHERE pe.event_type = 'login'
),
cohort AS (
SELECT
rs.user_id,
rs.signup_utc
FROM recent_signups rs
),
retained AS (
-- users with any login between day 1 and day 30 after signup (inclusive)
SELECT DISTINCT c.user_id
FROM cohort c
JOIN logins l
ON l.user_id = c.user_id
AND l.event_utc >= c.signup_utc + INTERVAL '1 day'
AND l.event_utc < c.signup_utc + INTERVAL '31 days'
)
SELECT
(SELECT COUNT(*) FROM cohort) AS cohort_size,
(SELECT COUNT(*) FROM retained) AS retained_30d,
CASE WHEN (SELECT COUNT(*) FROM cohort)=0 THEN 0
ELSE ROUND((SELECT COUNT(*) FROM retained)::numeric / (SELECT COUNT(*) FROM cohort) * 100, 2)
END AS retention_30d_pct;
