Case Study: Customer Cohort Analysis

-- CTE to identify customer acquisition cohort and activity periods
WITH customer_activities AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_date))::date AS cohort_date,
    DATE_TRUNC('month', order_date)::date AS activity_month
  FROM orders
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
),

-- CTE to assign period numbers relative to cohort start
cohort_analysis AS (
  SELECT
    customer_id,
    cohort_date,
    activity_month,
    1 AS has_activity,
    (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort_date)) * 12 +
    (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_date)) + 1 AS period_number
  FROM customer_activities
),

-- Calculate metrics by cohort and period
cohort_metrics AS (
  SELECT
    cohort_date,
    period_number,
    COUNT(DISTINCT customer_id) AS active_customers
  FROM cohort_analysis
  GROUP BY cohort_date, period_number
),

-- Calculate cohort sizes (customers in period 1)
cohort_sizes AS (
  SELECT
    cohort_date,
    active_customers AS cohort_size
  FROM cohort_metrics
  WHERE period_number = 1
)

-- Final result with retention rates
SELECT
  cm.cohort_date,
  cm.period_number,
  cm.active_customers,
  cs.cohort_size,
  ROUND((cm.active_customers * 100.0 / cs.cohort_size), 2) AS retention_rate
FROM cohort_metrics cm
JOIN cohort_sizes cs ON cm.cohort_date = cs.cohort_date
ORDER BY cm.cohort_date, cm.period_number;