MAU Growth Accounting — Solution & ExplanationProblems / MAU Growth Accounting / EditorialApproach

User-month grain via DISTINCT + month truncation.

Classify presence with windows. first_month (window MIN) and prev_active_month (LAG) turn each user-month row into a self-describing record: debut → new; previous month adjacent → retained; previous month older → resurrected. Conditional counts per month produce three of the four columns in one GROUP BY.

Count absence with a shifted anti-join. Churn can't be classified on the active rows — churned users have *no row* in the month being reported. Anti-join month M actives against M+1, and attribute the count to M+1.

LEFT JOIN + COALESCE. The first month picks up churned = 0; the anti-join's phantom month beyond the data (everyone "churns" after the last month) finds no classified row to join to and vanishes.

Key InsightGrowth accounting mixes two fundamentally different query shapes: presence classification (window functions over the rows that exist) and absence detection (anti-joins for the rows that don't). No single pass produces both — the standard trap is trying to bolt churn onto the window CTE and discovering there is no row to hang it on. Compute them separately, at their natural grains, and join at the end. The identity new + retained + resurrected = MAU (and churned(M) = MAU(M-1) − retained(M)) makes the report self-auditing.Hints

Two window functions classify every user-month in one pass: MIN(month) OVER user tells you if this is the debut; LAG(month) tells you whether the previous active month was adjacent (retained) or further back (resurrected).

Retained vs resurrected is one comparison: prev_active_month = month - 1 month vs < month - 1 month. LAG returns NULL only on the first month, which the new-users branch already claims.

Churned users do not APPEAR in the month you report them — they are defined by absence. Build churn separately: anti-join month M's actives against M+1, then label the result month M+1.

Glue with a LEFT JOIN from the classified months and COALESCE churn to 0 — the first month has no predecessor and must show churned = 0, and months whose churn lands past the data's end drop out naturally.

Sanity checks: new + retained + resurrected = that month's MAU, and churned(M) = MAU(M-1) - retained(M).

Solve MAU Growth Accounting yourself →