Growth Rates with LAG: SQL lesson

Year-over-year growth is a staple of business reporting. LAG puts last year's number next to this year's, and the rest is arithmetic.

This lesson uses yearly_revenue from the E-commerce database.

Put each year next to the one before it:

SELECT year, revenue,
       LAG(revenue) OVER (ORDER BY year) AS prev_revenue
FROM yearly_revenue;

Growth % = (this year - last year) / last year × 100:

SELECT year, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
             / LAG(revenue) OVER (ORDER BY year), 1) AS growth_pct
FROM yearly_revenue;

The first year has no previous row, so LAG returns NULL and its growth is NULL too. That's correct: there's nothing to compare it with.

Writing LAG twice works, but a CTE is easier to read:

WITH yearly AS (
    SELECT year, revenue,
           LAG(revenue) OVER (ORDER BY year) AS prev
    FROM yearly_revenue
)
SELECT year, ROUND(100.0 * (revenue - prev) / prev, 1) AS growth_pct
FROM yearly;

Examples run on the employees sample database. Open any of them in the playground to experiment.