What is your churn rate — and is it getting better or worse?

WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', signup_date)::date AS cohort_month
  FROM customers
),
activity AS (
  SELECT
    customer_id,
    MAX(order_date)::date              AS last_active
  FROM orders
  WHERE status NOT IN ('cancelled', 'refunded')
  GROUP BY customer_id
)
SELECT
  TO_CHAR(c.cohort_month, 'YYYY-MM')  AS cohort,
  COUNT(*)                             AS cohort_size,
  COUNT(*) FILTER (
    WHERE a.last_active < CURRENT_DATE - INTERVAL '90 days'
      OR a.last_active IS NULL
  )                                    AS churned,
  COUNT(*) FILTER (
    WHERE a.last_active >= CURRENT_DATE - INTERVAL '90 days'
  )                                    AS still_active,
  ROUND(
    COUNT(*) FILTER (
      WHERE a.last_active < CURRENT_DATE - INTERVAL '90 days'
        OR a.last_active IS NULL
    ) * 100.0 / NULLIF(COUNT(*), 0), 1
  )                                    AS churn_rate_pct
FROM cohorts c
LEFT JOIN activity a USING (customer_id)
WHERE c.cohort_month >= NOW() - INTERVAL '12 months'
  AND c.cohort_month < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months'
GROUP BY c.cohort_month
ORDER BY c.cohort_month ASC