Month-1 retention rate by signup cohort

Question

Growth reports the share of each monthly signup cohort that came back and transacted in the **following** calendar month.

`app_users` holds `signed_up_on`; `app_activity` holds one row per active day. Using CTEs, return `cohort_month`, `cohort_size`, `retained_users` and `retention_pct` (retained ÷

Accepted Answer

Step 1: In the `cohorts` CTE, bucket each user into their signup month with `DATE_TRUNC('month', signed_up_on)` cast back to `date`. Step 2: In the `retained` CTE, join activity to cohorts and keep only activity whose month equals the cohort month plus one month. `DISTINCT` collapses multiple active days into one retained user. Step 3: Left-join the cohort list to the retained list so cohorts with nobody returning still appear with zero. Step 4: `COUNT(*)` gives the cohort size while `COUNT(r.user_id)` ignores `NULL`s and therefore counts only retained users. Step 5: Compute the percentage as `COUNT(r.user_id) * 100.0 / COUNT(*)`. Multiplying by `100.0` forces numeric division instead of integer division; round to 1 decimal. Step 6: Group by `cohort_month` to produce one row per cohort.

Month-1 retention rate by signup cohort

More CTEs questions

Asked at Swiggy