LAG and LEAD
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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:
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.
LAG(x)LEAD(x)LAG(x, 3)LEAD(x, 1, 'none')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:
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.
-- Write your query below