sql · level 3 · unit 2 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
gives every group a window of its own.
So far each window covered the whole result. splits it into one partition per round, and the window function starts again in each. It is like GROUP BY, except no rows get squashed.
Inside OVER, write PARTITION BY first and ORDER BY second. Here each round gets its own podium, so every round has a first place.
Aggregates work too. AVG(score) OVER (PARTITION BY round) puts each round's average beside every entry in that round.
SELECT round, baker, score,
RANK() OVER (PARTITION BY round ORDER BY score DESC) AS place
FROM entries
ORDER BY round, place, baker;Partitions can use several columns, such as PARTITION BY city, day.
guess first
Only Ava and Leo. How does the count go?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT round, baker,
ROW_NUMBER() OVER (PARTITION BY round ORDER BY baker) AS n
FROM entries
WHERE baker IN ('Ava', 'Leo')
ORDER BY round, n;your turn
Your turn. Who beat their round the most?
Fill the blank so every entry is compared with the average of its own round. The three biggest wins over the average come first.
result
Your answer appears here as a table.
fix it
Every round should have a winner. Fix it!
result
Your answer appears here as a table.
- Only one baker came first. Should every round have its own first place?
- The window covers all twelve entries at once. It needs splitting into one partition per round.
- Add PARTITION BY round before ORDER BY inside OVER, then press run.
nailed it
every group got its own window. :D
PARTITION BYrestarts the window for every group.- Write
PARTITION BYbeforeORDER BYinsideOVER. - Unlike
GROUP BY, partitions keep every row.
new words in your collection
- PARTITION BY
- partition
This lesson isn’t saved. A free account remembers every lesson you finish.