Growth with LAG
- SQL course
- Advanced
- Lesson 22/81
- 5 min
Compute user growth with LAG.
LAG difference
You can compute day-over-day change with LAG. You take the difference with the previous day.
WITH dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events GROUP BY event_date
)
SELECT event_date, users,
users - LAG(users) OVER (ORDER BY event_date) AS growth
FROM dau;This query gives the daily growth amount.
WITH defines a temporary result set (CTE) named dau; the query after it uses it like a table. event_date, users, users - LAG(users) OVER (ORDER BY … is selected from dau. LAG() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
LAG difference — details
LAG(users) gives the previous day's value. Subtraction finds the daily change.
To see the change as a percentage you divide the difference by the previous value and multiply by 100; NULLIF is a safe guard because the divisor can be zero.
Practice LAG difference
Compute daily user change with users - LAG(users) OVER (ORDER BY event_date).
Starter code
-- daily growth
WITH dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events GROUP BY event_date
)
SELECT event_date, users,
users - LAG(users)Sample solution
WITH dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events GROUP BY event_date
)
SELECT event_date, users,
users - LAG(users) OVER (ORDER BY event_date) AS growth
FROM dau;Expected output
event_date users growth 2026-01-01 150 NULL 2026-01-02 180 30
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Growth with LAG: match the terms
| Term | Meaning |
|---|---|
LAG |
previous |
difference |
change |
growth |
increase |
time series |
day |
2 How is daily change computed?
- With SUM
- LAG ile fark Correct answer
- With COUNT
- AVG ile
Explanation: LAG gives the difference from the previous day.
3 Add LAG
users - ____(users) OVER (ORDER BY event_date) AS growth users - LAG(users) OVER (ORDER BY event_date) AS growthExplanation: LAG(users) gives the previous day's user count; the difference shows the growth.