SQL: Churn Rate by Cohort Month | Dataford Interview Questions

Your question is SQL: Churn Rate by Cohort Month. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

NewWave Telecom & Technologies wants to monitor customer retention in its NewWave Connect service. Write a PostgreSQL query that calculates monthly churn by customer cohort, where a cohort is defined by the month of a customer's first non-null usage event.

A customer is considered active in a month if at least one usage event exists. Churn occurs when a customer was active in the previous month but is inactive in the current month. The analysis should continue through the latest usage month in the data.

Requirements

Return cohort_month, month_number, cohort_size, active_customers, churned_customers, and churn_rate.
Use the previous month's active customer count as the churn-rate denominator.
Return a zero churn rate for the cohort's starting month and months with no previous active customers.
Include months with no active customers, and order the result by cohort month and month number.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique NewWave customer identifier
plan_name	VARCHAR(40)	NewWave Connect subscription plan

usage_events

Column	Type	Description
event_idPK	INTEGER	Unique usage event identifier
customer_id	INTEGER	Customer associated with the usage event
event_date	DATE	Date on which usage occurred
event_type	VARCHAR(30)	Type of NewWave service usage