sql · level 3 · unit 3 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
sets exactly which rows a window looks at.
One busy day can hide a trend. A smooths it out: each day shows the average of the last few days, and the window slides along with you.
The rows a window looks at for each row are its . ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means this row and the two before it, so AVG(amount) over that frame is a three-row average.
Other edges work too, such as 1 FOLLOWING or UNBOUNDED PRECEDING. Near the start the frame holds fewer rows, so the first row averages only itself.
SELECT day, amount,
ROUND(AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 1) AS avg_3
FROM takings
ORDER BY day;With 4 June missing, three rows are not quite three days. Unit 4 shows how to fill the gap.
guess first
One row back, one row ahead. What are the sums?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT day, amount,
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS nearby
FROM takings
ORDER BY day
LIMIT 3;your turn
Your turn. Choose how smooth to make it.
Pick how many earlier rows the frame should include, such as 3. Run it, then try a bigger number and watch the line get smoother.
result
Your answer appears here as a table.
fix it
The three-row average will not run. Fix it!
result
Your answer appears here as a table.
- The error points at
AND. What should come before a frame with two edges? - A frame with a start and an end reads
ROWS BETWEEN start AND end. - Add BETWEEN after ROWS on line 4, then press run.
nailed it
your trend line is smooth now. :D
- A frame is the set of rows a window looks at.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWcovers three rows.- A moving average smooths out busy and quiet days.
new words in your collection
- frame
- ROWS BETWEEN
- moving average
This lesson isn’t saved. A free account remembers every lesson you finish.