sql · level 3 · unit 3 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
looks at the row before. looks at the row after.
To spot change, compare each row with its neighbour. LAG(amount) OVER (ORDER BY day) fetches the amount from the row before, and LEAD(amount) fetches it from the row after. So amount - LAG(amount) OVER (...) is the change since last time.
The first row has nothing before it, so LAG gives NULL. A third argument sets a default: LAG(amount, 1, 0) means one row back, or 0 if there is none.
Careful: the row before is not always the day before. The café has no row for 4 June.
SELECT day, amount,
LAG(amount) OVER (ORDER BY day) AS previous,
amount - LAG(amount) OVER (ORDER BY day) AS change
FROM takings
ORDER BY day;With 4 June missing, 5 June is compared with 3 June.
guess first
The last three days, looking ahead. What shows?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT day, amount,
LEAD(amount, 1, 0) OVER (ORDER BY day) AS next_amount
FROM takings
WHERE day >= '2024-06-06'
ORDER BY day;your turn
Your turn. How did each stall do since yesterday?
Fill the blank with the function that fetches the stall's previous amount, so change shows the rise or fall since that stall's last day.
result
Your answer appears here as a table.
fix it
Each day's previous amount is its own amount. Fix it!
result
Your answer appears here as a table.
- Every row shows its own amount as the previous one. How many rows back is
LAGlooking? - The second argument is how many rows back. The default for a missing row comes third.
- Change LAG(amount, 0) to LAG(amount, 1, 0), then press run.
nailed it
you spotted every rise and fall. :D
LAGfetches from an earlier row,LEADfrom a later one.- The arguments are the column, how many rows away, then a default.
- The row before is not always the day before.
new words in your collection
- LAG
- LEAD
This lesson isn’t saved. A free account remembers every lesson you finish.