LAG and LEAD

Concepts covered: Row Offsets

LAG reaches back a fixed number of rows inside a window and returns the value it finds there. The window's ORDER BY decides which row counts as previous: with ORDER BY stream_date, the previous row is the previous day. A row keeps every column it had, and LAG attaches one more column holding a neighbour's value, so a row can be compared to the row before it without a join.

SELECT
  stream_date,
  streams,
  LAG(streams) OVER (ORDER BY stream_date) AS prev_day,
  streams - LAG(streams) OVER (ORDER BY stream_date) AS day_change
FROM daily_streams

The engine sorts the 5 rows by stream_date and walks them in that order. 2024-03-02 looks back 1 row and finds 1200, so prev_day is 1200 and day_change is 300. 2024-03-01 has no row before it, so prev_day is NULL, and 1200 minus NULL is NULL as well, because arithmetic with a NULL operand returns NULL. Every later row has a neighbour, so the remaining changes are -150, 750 and -200.

LEAD looks the other way

LEAD has the same shape and reaches forward. LEAD(streams) on 2024-03-01 returns 1500, the next day's number, and the last row gets NULL because nothing follows it. A row that knows tomorrow's value can answer which days were followed by a drop, and the CASE below labels each one.

SELECT
  stream_date,
  streams,
  LEAD(streams) OVER (ORDER BY stream_date) AS next_day,
  CASE
    WHEN LEAD(streams) OVER (ORDER BY stream_date) < streams THEN 'drop'
    ELSE 'hold'
  END AS tomorrow
FROM daily_streams

2024-03-02 and 2024-03-04 are labelled drop, since 1350 is below 1500 and 1900 is below 2100. The last row is labelled hold even though no next day exists: its next_day is NULL, the comparison NULL < 1900 is neither true nor false, and a CASE with no true branch falls through to ELSE. Three-valued logic reaches into window results exactly as it reaches into WHERE.

Offset and default

Both functions accept 2 optional arguments after the column. The second is the offset, how many rows to reach: LAG(streams, 7) returns the value from 7 rows back, the same weekday a week earlier when every day has a row. The third is a default returned when the reach lands outside the window: LAG(streams, 1, 0) turns the first row's NULL into 0, so the first day's change becomes 1200. Pick that default with care, because a change of 1200 on launch day and a missing comparison mean different things to the person reading the chart.

On the 5-row daily_streams table, what does LEAD(streams, 2) OVER (ORDER BY stream_date) return for the 2024-03-03 row?

• 2100, the value 1 row ahead on 2024-03-04
• 1500, the value 1 row back on 2024-03-02
• 1900, the value 2 rows ahead on 2024-03-05
• NULL, because the reach lands past the last row

Previous row within each artist

PARTITION BY resets the reach at every group boundary. With PARTITION BY artist, the first Nova row looks back and finds nothing, even though Kite's last row sits right before it in the table. Without the partition, ORDER BY stream_date interleaves the 2 artists by date, and a row's prev_day can come from the other artist, reporting a change that never happened. The query below uses the default argument, so the first day of each artist reads 0 instead of NULL.

SELECT
  artist,
  stream_date,
  streams,
  LAG(streams, 1, 0) OVER (
    PARTITION BY artist
    ORDER BY stream_date
  ) AS prev_day
FROM artist_streams

SELECT
  user_id,
  login_date,
  DATE_DIFF(
    'day',
    LAG(login_date) OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ),
    login_date
  ) AS days_since_last
FROM logins

About this lesson

This section is part of the Window Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.