Approach: build a cohort table of users with their signup week (cohort_week = week starting date), then for each cohort-user detect whether they made a purchase in week-0 (same calendar week as signup) and week-1 (the next calendar week). Aggregate per cohort to produce counts and percentages. I assume week boundaries use DATE_TRUNC('week', ...) (weeks starting on Monday in Postgres; adjust if your DB/start-day differs). NULLs: users with no purchases are treated as not retained (0). Multiple purchases in a window count once per user.SQL (Postgres syntax):sql
WITH signups AS (
-- get each user's signup_date and cohort_week (week start)
SELECT
user_id,
MIN(event_date) AS signup_date, -- if multiple signup events, take earliest
DATE_TRUNC('week', MIN(event_date))::date AS cohort_week
FROM events
WHERE event_type = 'signup'
GROUP BY user_id
),
purchases_window AS (
-- join signups to purchases and mark if purchase occurred in week0 or week1
SELECT
s.user_id,
s.cohort_week,
-- week-0 window: cohort_week .. cohort_week + 6 days (same calendar week)
MAX(CASE WHEN p.purchase_date BETWEEN s.cohort_week AND s.cohort_week + INTERVAL '6 days' THEN 1 ELSE 0 END) AS bought_week0,
-- week-1 window: cohort_week + 7 days .. cohort_week + 13 days
MAX(CASE WHEN p.purchase_date BETWEEN s.cohort_week + INTERVAL '7 days' AND s.cohort_week + INTERVAL '13 days' THEN 1 ELSE 0 END) AS bought_week1
FROM signups s
LEFT JOIN purchases p
ON p.user_id = s.user_id
-- optionally, restrict purchases to plausible range to speed up: AND p.purchase_date BETWEEN s.cohort_week AND s.cohort_week + INTERVAL '13 days'
GROUP BY s.user_id, s.cohort_week
)
SELECT
cohort_week,
COUNT(*) AS users_signed_up,
-- percent retained in week0: users with bought_week0 = 1 divided by total signups
ROUND(100.0 * SUM(bought_week0) / NULLIF(COUNT(*), 0), 2) AS retained_week0_pct,
ROUND(100.0 * SUM(bought_week1) / NULLIF(COUNT(*), 0), 2) AS retained_week1_pct
FROM purchases_window
GROUP BY cohort_week
ORDER BY cohort_week;
