LAG and LEAD

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Looking at the previous row

Numbering and ranking look at the whole window at once. Sometimes a row only needs to see its neighbour: how many days passed between two badges of the same trainer? LAG(column) fetches the value of the previous row, following the window's ORDER BY:

SELECT t.name, b.earned_on, LAG(b.earned_on) OVER (PARTITION BY b.trainer_id ORDER BY b.earned_on) AS previous FROM badges b INNER JOIN trainers t ON t.trainer_id = b.trainer_id ORDER BY t.name, b.earned_on;

Each badge now carries the date of the one before it. The first badge of each trainer has no previous one, so LAG returns NULL there — and thanks to PARTITION BY trainer_id, Morgan's badges never look at Alex's. With both dates on the same row, the gap is a subtraction with julianday():

SELECT t.name, b.earned_on, CAST(julianday(b.earned_on) - julianday(LAG(b.earned_on) OVER (PARTITION BY b.trainer_id ORDER BY b.earned_on)) AS INTEGER) AS days_since_previous FROM badges b INNER JOIN trainers t ON t.trainer_id = b.trainer_id ORDER BY t.name, b.earned_on;

Morgan's rhythm slowed down: 13 days, then 15, then 23 and 23. Alex waited 25 days, then 15; Taylor 11.

Offset and default

LAG(x, 2) looks two rows back; LAG(x, 1, 0) replaces the missing value with 0 instead of NULL. LEAD takes the same three arguments but looks forward.

Exercise 2 — Looking ahead with LEAD

LEAD() is the mirror of LAG: it reads the next row. When is the next battle, and how long until then?

SELECT battle_id, battle_date, LEAD(battle_date) OVER (ORDER BY battle_date) AS next_battle, CAST(julianday(LEAD(battle_date) OVER (ORDER BY battle_date)) - julianday(battle_date) AS INTEGER) AS days_until_next FROM battles;

The last battle has nothing after it: NULL. The longest pause of the season was 16 days, between February 15 and March 3. Both functions accept two more arguments: the second one changes the offset, the third one replaces the missing value. Back to LAG to see them at work:

SELECT battle_date, LAG(battle_date, 1, 'season start') OVER (ORDER BY battle_date) AS previous, LAG(battle_date, 2) OVER (ORDER BY battle_date) AS two_before FROM battles;

The first battle shows 'season start' instead of NULL, and two_before stays NULL for the first two rows, since nothing sits two rows back.

Exercise 3 — The first row

LAG(earned_on) OVER (PARTITION BY trainer_id ORDER BY earned_on), evaluated on a trainer's first badge, returns:

Options
• NULL — there is no previous row in that partition
• The last badge of the previous trainer
• The same date
• An error

Hint : Partitions are sealed: LAG never looks into another trainer's rows.

Exercise 4 — Next badge

Complete the query so that next_badge is the date of the next badge of the same trainer.

Hint : The function that looks forward, and the keyword that defines what "forward" means.

Exercise 5 — Neighbours

Match each expression with the value it returns.

Items to match
• LAG(x)
• LEAD(x)
• LAG(x, 3)
• LEAD(x, 1, 'none')
Definitions
• The value of the previous row
• The value of the next row
• The value three rows back
• The next value, or 'none' on the last row

Exercise 6 — First of the window

LAG and LEAD look at a neighbour. FIRST_VALUE(column) looks at the first row of the window — with the right ORDER BY, that is the team leader, repeated on every row of the team:

SELECT trainer_id, name, level, FIRST_VALUE(name) OVER (PARTITION BY trainer_id ORDER BY level DESC) AS team_leader, FIRST_VALUE(level) OVER (PARTITION BY trainer_id ORDER BY level DESC) - level AS gap_to_leader FROM codemon ORDER BY trainer_id, level DESC;

Each Codémon sees its team's leader and how far behind it is: Voltachu is 5 levels behind Aquachu, Firochu 4 behind Rockachu. You would expect LAST_VALUE to give the weakest of each team the same way. Try it:

SELECT trainer_id, name, level, LAST_VALUE(name) OVER (PARTITION BY trainer_id ORDER BY level DESC) AS weakest FROM codemon ORDER BY trainer_id, level DESC;

Every row is its own "last value"! By default, a window with an ORDER BY only extends from the first row up to the current row — so the last row it can see is always the current one. The next chapter names that boundary (the frame) and shows how to widen it.

Exercise 7 — The last battle

LEAD(battle_date) OVER (ORDER BY battle_date), evaluated on the most recent battle, returns:

Options
• NULL — there is no next row
• The first battle date, cycling back
• The same date
• 0

Hint : Unless a third argument gives a default, a missing neighbour is NULL.

Exercise 8 — The previous winner

Professor Pi wants to know who won the battle just before each one.

For every battle, return battle_date, winner (the winner's name) and previous_winner: the name of the winner of the previous battle in date order, or 'none' for the first battle (use the third argument of LAG). Sort by battle_date.

Starter code
-- Write your query below
Back to the course