Level 19: Window functions: LAG, LEAD and frames

Lesson

Comparing a row with the previous one is a frequent question: did the temperature rise since the day before? Is this month better than the previous one? LAG(column) OVER (ORDER BY …) returns the value of the previous row in the given order; LEAD returns that of the next row. temp_max - LAG(temp_max) OVER (ORDER BY day) gives the change compared with the day before.

The first row has no previous one: LAG is NULL there, and so is the change. Likewise, LEAD is NULL on the last row. LAG(x, 1, 0) returns 0 instead of NULL, and LAG(x, 2) goes back two rows.

With PARTITION BY, LAG and LEAD stay within the group: the first sale of each seller has no previous one, even if another seller's sale comes before it in the table.

You can also choose precisely which rows go into the calculation: this is the frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW takes the current row and the two before it. AVG(temp_max) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gives a 3-day moving average, which smooths out the variations. The first two rows have fewer neighbours: their average covers 1, then 2 values.

There are two ways to bound a frame. ROWS counts rows. RANGE reasons on the sort values: tied rows come in or go out together. Without a written frame, an OVER with ORDER BY uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: this is what explains the tied running totals of level 18. For a moving window, always write ROWS.

As with ranks, you do not filter on LAG in the WHERE of the same query: you compute it in a CTE, then filter on it, for example to find the months going up.

Syntax

SELECT d,
       x,
       x - LAG(x) OVER (ORDER BY d) AS change,
       AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg3
FROM my_table;

Worked example

SELECT month, amount, LAG(amount) OVER (ORDER BY month) AS previous
FROM sales
WHERE seller = 'Ana';

Each of Ana's months shows the previous month's amount; January has none (NULL).

Example result

Key points

• LAG = previous row, LEAD = next row.
• The first (or last) row gives NULL.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW = sliding window.

Common pitfalls

• Without ORDER BY inside OVER, “previous” has no meaning.
• Filtering WHERE amount > LAG(amount) … in the same query is not allowed: go through a CTE.

Going further

FIRST_VALUE(x) and LAST_VALUE(x) return the first and the last value of the frame.

The level’s 5 exercises

1. Guided · For Paris, show each day, the maximum temperature and its change from the day before (change). (Table: readings)
2. Practice · For each play, show the user, the time of the play and the time of their next play (next_play). (Table: plays)
3. Practice · For each city and each day, show the maximum temperature and its average over the last 3 days (the day itself and the 2 before), rounded to one decimal (avg3). (Table: readings)
4. Practice · All cities together, compute the running total of rain day after day in two ways: cum_range with the default frame (OVER (ORDER BY day)), and cum_rows row by row, with ROWS and a sort by day then by city. Show the city, the day, the rain and both running totals, sorted by day then by city. (Table: readings)
5. Challenge · Show the months in which a seller did better than the month before: seller, month and increase (growth). (Table: sales)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic