SQL Month-over-Month Growth
LAG plus a percentage formula — the standard shape of every growth-rate question.
What & Why
Month-over-month growth compares this month's total to last month's, as a percentage: (current - previous) / previous * 100. The "previous month" part is exactly what LAG is for.
See How It Works
BUSINESS QUESTION
Calculate month-over-month growth in new user signups.
| id | created_at | country | channel | plan | activated_at | churned_at |
|---|---|---|---|---|---|---|
| 101 | 2024-01-15 09:10:00+00 | US | organic | pro | 2024-01-16 14:25:00+00 | NULL |
| 102 | 2024-02-10 11:05:00+00 | CA | paid | free | NULL | 2024-03-20 10:00:00+00 |
| 103 | 2024-03-12 16:35:00+00 | GB | referral | pro | 2024-03-13 08:15:00+00 | NULL |
| 104 | 2024-04-01 13:20:00+00 | US | organic | free | 2024-04-03 12:00:00+00 | NULL |
EXAMPLE QUERY
WITH monthly AS (
SELECT DATE_TRUNC('month', created_at)::date AS month_start, COUNT(*) AS signups
FROM growth.users
GROUP BY 1
),
compared AS (
SELECT *, LAG(signups) OVER (ORDER BY month_start) AS previous_signups
FROM monthly
)
SELECT
month_start,
signups,
previous_signups,
ROUND((signups - previous_signups)::numeric / NULLIF(previous_signups, 0) * 100, 2) AS growth_pct
FROM compared
ORDER BY month_start;RESULT — monthly signup comparison
| month_start | signups | previous_signups | growth_pct |
|---|---|---|---|
| 2024-01-01 | 1 | NULL | NULL |
| 2024-02-01 | 1 | 1 | 0.00 |
| 2024-03-01 | 1 | 1 | 0.00 |
| 2024-04-01 | 1 | 1 | 0.00 |
Each representative month contains one signup.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario