Interview Question Bank · Monthly activity sets · LEFT JOIN anti-match · zero-churn months
16. Monthly customer churn from orders
HardE-commerceQuestion #283
Concept: Monthly activity sets · LEFT JOIN anti-match · zero-churn monthsshow
Churn from activity data compares two sets: customers active last month and customers active this month. Build one DISTINCT row per customer per active month, then join each month's actives (the denominator) to the next month's activity with a LEFT JOIN; rows with no match churned. Count churners with COUNT(*) FILTER instead of filtering them in WHERE, so a month with no churn still reports 0.
Example (different data):
WITH a AS (
SELECT DISTINCT member_id, date_trunc('month', visited_at)::date AS m
FROM gym_checkins
)
SELECT prev.m + INTERVAL '1 month' AS month,
COUNT(*) AS members_last_month,
COUNT(*) FILTER (WHERE cur.member_id IS NULL) AS lapsed
FROM a prev
LEFT JOIN a cur ON cur.member_id = prev.member_id
AND cur.m = prev.m + INTERVAL '1 month'
GROUP BY prev.m;Your task
Brindlewood Market counts a customer as active in a month if they placed at least one completed order that month. Leadership wants monthly churn for February to June 2024: of last month's active customers, what share placed no completed order this month?
For each month from 2024-02 to 2024-06, return month (YYYY-MM), active_last_month (customers active in the previous month), churned (of those, customers not active this month) and churn_rate_pct (churned as a % of active_last_month, rounded to 1 decimal). Sort by month.
Starting Postgres in your browser…
Checking your access…