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.
Output details
- Treat signup dates through
2024-06-30 and churn dates from signup inclusive through 2024-09-30 inclusive.
- Exclude churn events whose driver is
NULL.
- Count orders dated from signup inclusive through churn exclusive, without requiring a completion-status condition.
- Treat customers with no pre-churn orders as
0 orders and include them in the average.
- Round
churn_rate_pct and avg_pre_churn_orders to two decimal places.
- Rank churn count descending, average orders descending, and driver name ascending; sort the final rows by cohort ascending, rank ascending, then driver ascending.
