LAG and LEAD in SQL

LAG looks back a row, LEAD looks forward — growth rates without a self join.

SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (
        ORDER BY month
    ) AS previous_month,
    revenue - LAG(revenue, 1, 0) OVER (
        ORDER BY month
    ) AS change
FROM monthly_revenue
ORDER BY month;

How it works

The second argument is the offset, the third a default.
Subtracting the previous row gives period-over-period change.
Ordering inside the window defines what previous means.

Keywords and builtins used here

AS
BY
FROM
ORDER
SELECT
month

The run, in numbers

Lines: 11
Characters to type: 184
Tokens: 46
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 29 seconds.

Type this snippet

Step 4 of 9 in Window functions, step 10 of 24 in Analytics & reporting.

← Previous Next →