Monthly Churn Detection — Solution & ExplanationProblems / Monthly Churn Detection / EditorialApproach

User-month grain. SELECT DISTINCT user_id, DATE_TRUNC('month', event_time) — everything downstream assumes one row per user per active month.

Shifted self anti-join. LEFT JOIN monthly to itself: same user, right side's month equal to the left month plus one. A missing right row means the user vanished next month — that's the churn flag. Keeping it as a 0/1 CASE (instead of filtering) lets one GROUP BY produce both the denominator (COUNT(*)) and the numerator (SUM(churned)).

Drop the last month. Its "next month" lies beyond the data, so every user would look churned; the month < MAX(month) guard removes the artifact.

Rate. churned * 100.0 / active, 2 decimals, ordered by month.

Key InsightChurn is a statement about absence, and SQL only stores presence — so churn queries are anti-joins at heart. The two classic bugs are both boundary artifacts: forgetting the last-month guard (everyone "churns" from the final month) and comparing raw timestamps instead of truncated months (a user active May 30 and June 2 must count as retained, which only works after month truncation).Hints

Collapse to distinct (user, month) pairs first. Churn is defined on months, and duplicate events inside a month would corrupt every count downstream.

"Active in M but not in M+1" is an anti-join: LEFT JOIN the user-month set to itself shifted by one month (n.month = m.month + INTERVAL 1 MONTH) and keep track of the rows with no match.

The final month can't have churn measured against a month that doesn't exist yet — filter it out with a scalar subquery on MAX(month), not by hardcoding a date.

In the shifted self-join, a NULL right side is the churn signal; SUM a CASE flag rather than filtering, because you need active_users (all rows) and churned_users (flagged rows) from the same grouping.

Solve Monthly Churn Detection yourself →