Growth with LAG

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.

Exercise LAG difference

Compute daily user change with users - LAG(users) OVER (ORDER BY event_date).

Show example 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;

Test yourself

Growth with LAG: match the terms

LAG

previous
difference

change
growth

increase
time series

day
How is daily change computed?
- With SUM
- LAG ile fark
- With COUNT
- AVG ile
Show answer

LAG ile fark — LAG gives the difference from the previous day.

Add LAG

  users - ____(users) OVER (ORDER BY event_date) AS growth

Show answer

  users - LAG(users) OVER (ORDER BY event_date) AS growth

Next lesson: Final report

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies