Part 100: Practical Project: Month-over-Month Growth & Churn Analysis

Let's compute standard SaaS recurring revenue and growth rates using Window Functions and CTEs.

The Query

WITH MonthlyMetrics AS (
  SELECT 
    DATE_TRUNC('month', payment_date) AS mrr_month,
    SUM(amount) AS mrr
  FROM subscription_payments
  WHERE status = 'succeeded'
  GROUP BY 1
),
CalculatedGrowth AS (
  SELECT 
    mrr_month,
    mrr,
    LAG(mrr) OVER (ORDER BY mrr_month) AS prev_mrr
  FROM MonthlyMetrics
)
SELECT 
  TO_CHAR(mrr_month, 'YYYY-MM') AS month,
  mrr,
  COALESCE(prev_mrr, 0) AS previous_month_mrr,
  ROUND(((mrr - prev_mrr) / NULLIF(prev_mrr, 0)) * 100, 2) AS mom_growth_pct
FROM CalculatedGrowth
ORDER BY mrr_month;

Key Takeaways

• Combines date truncations, window lags, zero-division safeguards (NULLIF), and growth percentages.

In Part 101, we enter Module 11 to master Built-in Functions & Expressions!