WITH monthly_mrr AS (
  SELECT 
  DATE_FORMAT(period_start, '%Y-%m') as month,
  customer_id,
  SUM(mrr_amount) as mrr
  FROM subscriptions
  WHERE status = 'active'
  GROUP BY DATE_FORMAT(period_start, '%Y-%m'), customer_id
 ),
 mrr_changes AS (
  SELECT 
  curr.month,
  curr.customer_id,
  curr.mrr as current_mrr,
  COALESCE(prev.mrr, 0) as previous_mrr,
  CASE 
  WHEN prev.mrr IS NULL THEN 'new'
  WHEN curr.mrr > prev.mrr THEN 'expansion'
  WHEN curr.mrr < prev.mrr THEN 'contraction'
  ELSE 'retained'
  END as mrr_type
  FROM monthly_mrr curr
  LEFT JOIN monthly_mrr prev ON curr.customer_id = prev.customer_id
  AND prev.month = DATE_FORMAT(
  DATE_SUB(STR_TO_DATE(CONCAT(curr.month, '-01'), '%Y-%m-%d'), 
  INTERVAL 1 MONTH), '%Y-%m')
 )
 SELECT 
  month,
  SUM(CASE WHEN mrr_type = 'new' THEN current_mrr ELSE 0 END) as new_mrr,
  SUM(CASE WHEN mrr_type = 'expansion' THEN current_mrr - previous_mrr ELSE 0 END) as expansion_mrr,
  SUM(CASE WHEN mrr_type = 'contraction' THEN previous_mrr - current_mrr ELSE 0 END) as contraction_mrr,
  SUM(current_mrr) as total_mrr
 FROM mrr_changes
 GROUP BY month
 ORDER BY month;

WITH monthly_metrics AS (
  SELECT 
  DATE_FORMAT(period_start, '%Y-%m') as month,
  COUNT(DISTINCT customer_id) as active_customers,
  SUM(mrr_amount) as mrr,
  COUNT(DISTINCT CASE WHEN canceled_at IS NOT NULL 
  THEN customer_id END) as churned_customers
  FROM subscriptions
  GROUP BY DATE_FORMAT(period_start, '%Y-%m')
 )
 SELECT 
  month,
  active_customers,
  churned_customers,
  ROUND(100.0 * churned_customers / 
  NULLIF(LAG(active_customers) OVER (ORDER BY month), 0), 2) as customer_churn_rate,
  mrr
 FROM monthly_metrics
 ORDER BY month;

WITH customer_cohorts AS (
  SELECT 
  customer_id,
  DATE_FORMAT(created_at, '%Y-%m') as cohort_month
  FROM customers
 ),
 monthly_revenue AS (
  SELECT 
  c.cohort_month,
  DATE_FORMAT(s.period_start, '%Y-%m') as revenue_month,
  COUNT(DISTINCT s.customer_id) as customers,
  SUM(s.mrr_amount) as mrr
  FROM customer_cohorts c
  INNER JOIN subscriptions s ON c.customer_id = s.customer_id
  WHERE s.status = 'active'
  GROUP BY c.cohort_month, DATE_FORMAT(s.period_start, '%Y-%m')
 )
 SELECT 
  cohort_month,
  revenue_month,
  customers,
  mrr,
  ROUND(100.0 * customers / FIRST_VALUE(customers) OVER (
  PARTITION BY cohort_month ORDER BY revenue_month
  ), 1) as customer_retention_pct
 FROM monthly_revenue
 ORDER BY cohort_month, revenue_month;

WITH customer_revenue AS (
  SELECT 
  c.customer_id,
  c.acquisition_channel,
  SUM(s.mrr_amount) as total_revenue,
  COUNT(DISTINCT DATE_FORMAT(s.period_start, '%Y-%m')) as months_active
  FROM customers c
  LEFT JOIN subscriptions s ON c.customer_id = s.customer_id
  GROUP BY c.customer_id, c.acquisition_channel
 )
 SELECT 
  acquisition_channel,
  COUNT(*) as customers,
  ROUND(AVG(total_revenue), 2) as avg_cltv,
  ROUND(AVG(months_active), 1) as avg_lifetime_months
 FROM customer_revenue
 WHERE total_revenue > 0
 GROUP BY acquisition_channel
 ORDER BY avg_cltv DESC;