411. Cohort Retention Analysis

WITH first_order AS (SELECT customer_name, MIN(strftime('%Y-%m', order_date)) AS cohort_month FROM orders GROUP BY customer_name), repeat_customers AS (SELECT DISTINCT o.customer_name, f.cohort_month FROM orders o JOIN first_order f ON o.customer_name = f.customer_name WHERE strftime('%Y-%m', o.order_date) > f.cohort_month) SELECT f.cohort_month, COUNT(DISTINCT f.customer_name) AS cohort_size, COUNT(DISTINCT r.customer_name) AS retained_customers FROM first_order f LEFT JOIN repeat_customers r ON f.customer_name = r.customer_name AND f.cohort_month = r.cohort_month GROUP BY f.cohort_month ORDER BY f.cohort_month;