Monthly Revenue Growth Analysis

basic

A SaaS company needs to track monthly recurring revenue (MRR) growth. They want to see each month's revenue alongside the previous month's revenue and calculate both absolute and percentage growth rates.

-- Monthly revenue growth analysis with LAG

WITH monthly_revenue AS (

SELECT

DATE_TRUNC('month', subscription_date) as month,

SUM(monthly_amount) as total_mrr,

COUNT(DISTINCT customer_id) as active_customers,

AVG(monthly_amount) as avg_revenue_per_customer

FROM subscriptions

WHERE subscription_date >= '2024-01-01'

AND status = 'active'

GROUP BY DATE_TRUNC('month', subscription_date)

),

growth_analysis AS (

SELECT

month,

total_mrr,

active_customers,

ROUND(avg_revenue_per_customer, 2) as avg_revenue_per_customer,

-- LAG to get previous month's values

LAG(total_mrr, 1, 0) OVER (ORDER BY month) as prev_month_mrr,

LAG(active_customers, 1, 0) OVER (ORDER BY month) as prev_month_customers,

-- Calculate absolute changes

total_mrr - LAG(total_mrr, 1, 0) OVER (ORDER BY month) as mrr_change,

active_customers - LAG(active_customers, 1, 0) OVER (ORDER BY month) as customer_change,

-- Calculate percentage changes (handle division by zero)

CASE

WHEN LAG(total_mrr, 1, 0) OVER (ORDER BY month) = 0 THEN NULL

ELSE ROUND(

(total_mrr - LAG(total_mrr, 1, 0) OVER (ORDER BY month)) * 100.0 /

LAG(total_mrr, 1, 0) OVER (ORDER BY month), 2

)

END as mrr_growth_percent

FROM monthly_revenue

)

SELECT

TO_CHAR(month, 'YYYY-MM') as month,

'$' || TO_CHAR(total_mrr, 'FM999,999') as current_mrr,