SQL Year-over-Year Growth
The identical formula as Month-over-Month — just comparing against 12 periods back instead of 1.
What & Why
Year-over-year growth is the same LAG + percentage-change formula as month-over-month — the only difference is comparing each period to the same period one year earlier instead of the immediately preceding one.
See How It Works
BUSINESS QUESTION
Product compares feature releases in 2024 with the real 2023 release represented in product.features.
| id | name | description | team | released_at | deprecated_at |
|---|---|---|---|---|---|
| 1 | Activation Checklist | Guides new users through setup | growth | 2024-01-20 | NULL |
| 2 | CSV Export | Exports report data | platform | 2024-02-14 | NULL |
| 3 | Invite Nudges | Prompts workspace collaboration | growth | 2024-03-05 | NULL |
| 4 | Legacy Dashboard | Original reporting surface | analytics | 2023-08-10 | 2024-06-01 |
EXAMPLE QUERY
WITH yearly AS (
SELECT EXTRACT(YEAR FROM released_at)::int AS release_year, COUNT(*) AS features_released
FROM product.features
WHERE released_at IS NOT NULL
GROUP BY release_year
), compared AS (
SELECT
release_year,
features_released,
LAG(features_released) OVER (ORDER BY release_year) AS prior_year_releases
FROM yearly
)
SELECT
release_year,
features_released,
prior_year_releases,
ROUND((features_released - prior_year_releases)::numeric / NULLIF(prior_year_releases, 0) * 100, 2) AS yoy_growth_pct
FROM compared
ORDER BY release_year;RESULT — feature releases compared year over year
| release_year | features_released | prior_year_releases | yoy_growth_pct |
|---|---|---|---|
| 2023 | 1 | NULL | NULL |
| 2024 | 3 | 1 | 200.00 |
The fixture contains one 2023 feature and three 2024 features, so the 2024 release count grew by 200%.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario