SQL Cohort Analysis and Retention Interview Questions

-- events table
user_id    INTEGER
event_type VARCHAR   -- 'app_open', 'like', 'comment', 'purchase', etc.
event_date DATE

-- DAU
SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_type = 'app_open'
GROUP BY event_date
ORDER BY event_date;

-- WAU
SELECT
    DATE_TRUNC('week', event_date) AS week_start,
    COUNT(DISTINCT user_id) AS wau
FROM events
WHERE event_type = 'app_open'
GROUP BY DATE_TRUNC('week', event_date)
ORDER BY week_start;

-- MAU
SELECT
    DATE_TRUNC('month', event_date) AS month_start,
    COUNT(DISTINCT user_id) AS mau
FROM events
WHERE event_type = 'app_open'
GROUP BY DATE_TRUNC('month', event_date)
ORDER BY month_start;

WITH daily_active AS (
    SELECT
        event_date,
        COUNT(DISTINCT user_id) AS dau
    FROM events
    WHERE event_type = 'app_open'
    GROUP BY event_date
)
SELECT
    event_date,
    dau,
    AVG(dau) OVER (
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg_dau
FROM daily_active
ORDER BY event_date;

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', event_date) AS month_start,
        COUNT(DISTINCT user_id)         AS mau
    FROM events
    WHERE event_type = 'app_open'
    GROUP BY 1
),
daily AS (
    SELECT
        DATE_TRUNC('month', event_date) AS month_start,
        event_date,
        COUNT(DISTINCT user_id)         AS dau
    FROM events
    WHERE event_type = 'app_open'
    GROUP BY 1, 2
),
daily_avg AS (
    SELECT
        month_start,
        AVG(dau) AS avg_dau
    FROM daily
    GROUP BY 1
)
SELECT
    m.month_start,
    m.mau,
    da.avg_dau,
    ROUND(da.avg_dau / m.mau, 3) AS dau_mau_ratio
FROM monthly m
JOIN daily_avg da USING (month_start)
ORDER BY m.month_start;

-- users table
user_id     INTEGER
signup_date DATE

-- events table
user_id     INTEGER
event_date  DATE
event_type  VARCHAR

WITH first_activity AS (
    SELECT
        user_id,
        MIN(event_date)                              AS first_active_date,
        DATE_TRUNC('month', MIN(event_date))         AS cohort_month
    FROM events
    GROUP BY user_id
)
SELECT
    user_id,
    first_active_date,
    cohort_month
FROM first_activity
ORDER BY cohort_month, user_id;

WITH first_activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(event_date)) AS cohort_month
    FROM events
    GROUP BY user_id
),
user_activity AS (
    SELECT
        e.user_id,
        fa.cohort_month,
        DATE_TRUNC('month', e.event_date) AS activity_month
    FROM events e
    JOIN first_activity fa ON e.user_id = fa.user_id
),
month_offsets AS (
    SELECT
        cohort_month,
        activity_month,
        (
            (EXTRACT(YEAR  FROM activity_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
          + (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_month))
        ) AS months_since_acquisition,
        COUNT(DISTINCT user_id) AS retained_users
    FROM user_activity
    GROUP BY 1, 2, 3
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM first_activity
    GROUP BY cohort_month
)
SELECT
    mo.cohort_month,
    cs.cohort_size,
    mo.months_since_acquisition,
    mo.retained_users,
    ROUND(
        100.0 * mo.retained_users / cs.cohort_size, 1
    ) AS retention_pct
FROM month_offsets mo
JOIN cohort_sizes cs ON mo.cohort_month = cs.cohort_month
WHERE mo.months_since_acquisition BETWEEN 0 AND 6
ORDER BY mo.cohort_month, mo.months_since_acquisition;

-- logins table
user_id    INTEGER
login_date DATE

WITH first_login AS (
    SELECT
        user_id,
        MIN(login_date) AS first_date
    FROM logins
    GROUP BY user_id
)
SELECT
    fl.first_date,
    COUNT(DISTINCT fl.user_id)                                           AS cohort_size,
    COUNT(DISTINCT CASE WHEN l1.login_date = fl.first_date + INTERVAL '1 day'
                        THEN l1.user_id END)                            AS day1_retained,
    COUNT(DISTINCT CASE WHEN l7.login_date = fl.first_date + INTERVAL '7 days'
                        THEN l7.user_id END)                            AS day7_retained,
    ROUND(
        100.0 * COUNT(DISTINCT CASE WHEN l1.login_date = fl.first_date + INTERVAL '1 day'
                                    THEN l1.user_id END)
              / COUNT(DISTINCT fl.user_id), 1
    )                                                                    AS day1_retention_pct,
    ROUND(
        100.0 * COUNT(DISTINCT CASE WHEN l7.login_date = fl.first_date + INTERVAL '7 days'
                                    THEN l7.user_id END)
              / COUNT(DISTINCT fl.user_id), 1
    )                                                                    AS day7_retention_pct
FROM first_login fl
LEFT JOIN logins l1
    ON fl.user_id = l1.user_id
    AND l1.login_date = fl.first_date + INTERVAL '1 day'
LEFT JOIN logins l7
    ON fl.user_id = l7.user_id
    AND l7.login_date = fl.first_date + INTERVAL '7 days'
GROUP BY fl.first_date
ORDER BY fl.first_date;

WITH first_login AS (
    SELECT
        user_id,
        MIN(login_date) AS first_date
    FROM logins
    GROUP BY user_id
),
future_activity AS (
    SELECT
        fl.user_id,
        fl.first_date,
        MAX(l.login_date) AS last_seen_date
    FROM first_login fl
    LEFT JOIN logins l
        ON fl.user_id = l.user_id
        AND l.login_date > fl.first_date
    GROUP BY fl.user_id, fl.first_date
)
SELECT
    first_date,
    COUNT(DISTINCT user_id)                                                 AS cohort_size,
    COUNT(DISTINCT CASE WHEN last_seen_date >= first_date + INTERVAL '30 days'
                        THEN user_id END)                                   AS rolling_day30_retained,
    ROUND(
        100.0 * COUNT(DISTINCT CASE WHEN last_seen_date >= first_date + INTERVAL '30 days'
                                    THEN user_id END)
              / COUNT(DISTINCT user_id), 1
    )                                                                        AS rolling_day30_pct
FROM future_activity
WHERE first_date <= CURRENT_DATE - INTERVAL '30 days'
GROUP BY first_date
ORDER BY first_date;

-- Step 1: Build the date spine
WITH date_spine AS (
    SELECT
        generate_series(
            DATE_TRUNC('month', (SELECT MIN(login_date) FROM logins)),
            DATE_TRUNC('month', (SELECT MAX(login_date) FROM logins)),
            INTERVAL '1 month'
        )::DATE AS month_date
),
-- Step 2: Build cohorts
cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(login_date))::DATE AS cohort_month
    FROM logins
    GROUP BY user_id
),
-- Step 3: Generate every cohort x period combination
cohort_periods AS (
    SELECT DISTINCT
        c.cohort_month,
        ds.month_date AS activity_month,
        (
            (EXTRACT(YEAR  FROM ds.month_date) - EXTRACT(YEAR  FROM c.cohort_month)) * 12
          + (EXTRACT(MONTH FROM ds.month_date) - EXTRACT(MONTH FROM c.cohort_month))
        ) AS period_number
    FROM cohorts c
    CROSS JOIN date_spine ds
    WHERE ds.month_date >= c.cohort_month
),
-- Step 4: Compute actual retained users
actual_retention AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', l.login_date)::DATE AS activity_month,
        COUNT(DISTINCT c.user_id) AS retained_users
    FROM cohorts c
    JOIN logins l ON c.user_id = l.user_id
    GROUP BY 1, 2
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM cohorts GROUP BY cohort_month
)
SELECT
    cp.cohort_month,
    cp.period_number,
    cs.cohort_size,
    COALESCE(ar.retained_users, 0)      AS retained_users,
    ROUND(
        100.0 * COALESCE(ar.retained_users, 0) / cs.cohort_size, 1
    )                                   AS retention_pct
FROM cohort_periods cp
JOIN cohort_sizes cs ON cp.cohort_month = cs.cohort_month
LEFT JOIN actual_retention ar
    ON cp.cohort_month   = ar.cohort_month
    AND cp.activity_month = ar.activity_month
ORDER BY cp.cohort_month, cp.period_number;

-- user_activity table
user_id       INTEGER
activity_date DATE

-- subscriptions table
user_id    INTEGER
plan_id    VARCHAR
start_date DATE
end_date   DATE    -- NULL if still active

WITH monthly_activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_date)::DATE AS activity_month
    FROM user_activity
    GROUP BY 1, 2
),
with_lag AS (
    SELECT
        user_id,
        activity_month,
        LAG(activity_month) OVER (
            PARTITION BY user_id
            ORDER BY activity_month
        ) AS prev_activity_month
    FROM monthly_activity
),
gap_flagged AS (
    SELECT
        user_id,
        activity_month,
        prev_activity_month,
        -- Gap > 1 month means the user skipped at least one month
        CASE WHEN prev_activity_month IS NULL THEN 'first_seen'
             WHEN activity_month > prev_activity_month + INTERVAL '1 month' THEN 'returned_after_churn'
             ELSE 'active'
        END AS status
    FROM with_lag
)
SELECT *
FROM gap_flagged
ORDER BY user_id, activity_month;

WITH monthly_activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_date)::DATE AS activity_month
    FROM user_activity
    GROUP BY 1, 2
),
with_lead AS (
    SELECT
        user_id,
        activity_month,
        LEAD(activity_month) OVER (
            PARTITION BY user_id
            ORDER BY activity_month
        ) AS next_activity_month
    FROM monthly_activity
)
SELECT
    user_id,
    activity_month          AS last_active_month,
    next_activity_month     AS returned_month,
    activity_month + INTERVAL '1 month' AS churn_month
FROM with_lead
WHERE
    next_activity_month IS NULL
    OR next_activity_month > activity_month + INTERVAL '1 month'
ORDER BY user_id;

WITH last_seen AS (
    SELECT
        user_id,
        MAX(activity_date) AS last_activity_date
    FROM user_activity
    GROUP BY user_id
)
SELECT
    user_id,
    last_activity_date,
    CURRENT_DATE - last_activity_date         AS days_since_active,
    CASE
        WHEN CURRENT_DATE - last_activity_date >= 90 THEN 'churned'
        WHEN CURRENT_DATE - last_activity_date >= 30 THEN 'at_risk'
        ELSE 'active'
    END                                        AS churn_status
FROM last_seen
ORDER BY days_since_active DESC;

WITH cohort_base AS (
    SELECT
        user_id,
        plan_id,
        DATE_TRUNC('month', start_date)::DATE AS cohort_month,
        start_date,
        end_date
    FROM subscriptions
),
cohort_sizes AS (
    SELECT
        cohort_month,
        plan_id,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM cohort_base
    GROUP BY 1, 2
),
retention AS (
    SELECT
        cb.cohort_month,
        cb.plan_id,
        cs.cohort_size,
        -- Month 1: still active 1 month after start
        COUNT(DISTINCT CASE
            WHEN cb.end_date IS NULL
              OR cb.end_date >= cb.cohort_month + INTERVAL '2 months'
            THEN cb.user_id END)                                    AS month_1_retained,
        -- Month 2
        COUNT(DISTINCT CASE
            WHEN cb.end_date IS NULL
              OR cb.end_date >= cb.cohort_month + INTERVAL '3 months'
            THEN cb.user_id END)                                    AS month_2_retained,
        -- Month 3
        COUNT(DISTINCT CASE
            WHEN cb.end_date IS NULL
              OR cb.end_date >= cb.cohort_month + INTERVAL '4 months'
            THEN cb.user_id END)                                    AS month_3_retained
    FROM cohort_base cb
    JOIN cohort_sizes cs
        ON cb.cohort_month = cs.cohort_month
        AND cb.plan_id = cs.plan_id
    GROUP BY 1, 2, 3
)
SELECT
    cohort_month,
    plan_id,
    cohort_size,
    ROUND(100.0 * month_1_retained / cohort_size, 1) AS month_1_pct,
    ROUND(100.0 * month_2_retained / cohort_size, 1) AS month_2_pct,
    ROUND(100.0 * month_3_retained / cohort_size, 1) AS month_3_pct
FROM retention
ORDER BY cohort_month, plan_id;

-- As of a specific reference date (e.g., today)
WITH window_activity AS (
    SELECT
        user_id,
        COUNT(DISTINCT activity_date) AS days_active_l28
    FROM user_activity
    WHERE activity_date > CURRENT_DATE - INTERVAL '28 days'
      AND activity_date <= CURRENT_DATE
    GROUP BY user_id
),
-- Join back to all users who were ever active (to include 0-day users)
all_users AS (
    SELECT DISTINCT user_id FROM user_activity
)
SELECT
    au.user_id,
    COALESCE(wa.days_active_l28, 0) AS days_active_l28
FROM all_users au
LEFT JOIN window_activity wa ON au.user_id = wa.user_id
ORDER BY days_active_l28 DESC;

WITH window_activity AS (
    SELECT
        user_id,
        COUNT(DISTINCT activity_date) AS days_active_l28
    FROM user_activity
    WHERE activity_date > CURRENT_DATE - INTERVAL '28 days'
      AND activity_date <= CURRENT_DATE
    GROUP BY user_id
)
SELECT
    days_active_l28,
    COUNT(user_id)                                        AS user_count,
    ROUND(100.0 * COUNT(user_id) / SUM(COUNT(user_id)) OVER (), 1) AS pct_of_mau
FROM window_activity
GROUP BY days_active_l28
ORDER BY days_active_l28;

WITH first_week AS (
    SELECT
        user_id,
        DATE_TRUNC('week', MIN(activity_date))::DATE AS cohort_week
    FROM user_activity
    GROUP BY user_id
),
weekly_activity AS (
    SELECT
        ua.user_id,
        fw.cohort_week,
        DATE_TRUNC('week', ua.activity_date)::DATE AS activity_week,
        -- Weeks since acquisition
        (ua.activity_date - fw.cohort_week) / 7    AS week_number
    FROM user_activity ua
    JOIN first_week fw ON ua.user_id = fw.user_id
),
cohort_sizes AS (
    SELECT cohort_week, COUNT(DISTINCT user_id) AS size
    FROM first_week
    GROUP BY cohort_week
)
SELECT
    wa.cohort_week,
    cs.size                                                       AS cohort_size,
    wa.week_number,
    COUNT(DISTINCT wa.user_id)                                    AS retained_users,
    ROUND(
        100.0 * COUNT(DISTINCT wa.user_id) / cs.size, 1
    )                                                             AS retention_pct
FROM weekly_activity wa
JOIN cohort_sizes cs ON wa.cohort_week = cs.cohort_week
WHERE wa.week_number BETWEEN 0 AND 12
GROUP BY wa.cohort_week, cs.size, wa.week_number
ORDER BY wa.cohort_week, wa.week_number;