SQL for Churn Cohorts | Dataford Interview Questions

Write a SQL query to analyze customer churn by cohort and identify the top drivers of churn.

Use customer sign-up cohorts through June 2024 and consider churn events from the sign-up date through September 30, 2024. For each churn driver, include the number of affected customers and their average completed orders before churn.

Output

One row per cohort and churn driver, limited to the top three drivers within each cohort.
Columns: cohort_month, churn_driver, churned_customers, cohort_customers, churn_rate_pct, avg_pre_churn_orders, and driver_rank.
Sort by cohort_month ascending and driver_rank ascending, with churn count, average orders, and driver name resolving ties.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
signup_date	DATE	Customer signup date

churn_events

Column	Type	Description
churn_event_idPK	INT	Unique churn event identifier
customer_id	INT	Customer associated with the churn event
churn_date	DATE	Date the customer churned
churn_reason	VARCHAR(100)	Reported primary churn driver

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	NUMERIC(10,2)	Order subtotal

Your question is SQL for Churn Cohorts. Start with the requirements and the three tables in the Question tab.

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

Problem