Window Functions
Numbering rows without grouping.
Group by is great, but it has a cost. It squashes rows together. Ask for the average price per genre, and you lose the individual books. But what if Helen wants to see every book, and its genre's average, side by side? That's what window functions do. They calculate across a group of rows, without squashing them.

- GROUP BY squashes rows
- Keep every row
- And still see the group's number
What GROUP BY gives
Here's the group by version. Run it. One row per genre. The individual books are gone.
SELECT genre, ROUND(AVG(price), 2) AS genre_avg
FROM books
GROUP BY genre
LIMIT 3;โโโโโโโโโโโโฌโโโโโโโโโโโโ โ genre โ genre_avg โ โโโโโโโโโโโโผโโโโโโโโโโโโค โ Business โ 21.99 โ โ Cooking โ 24.82 โ โ Fantasy โ 17.82 โ โโโโโโโโโโโโดโโโโโโโโโโโโ
OVER and PARTITION BY
Now the window version. The same average, followed by over. Inside the brackets, partition by genre means, work out the average separately for each genre. But every book keeps its own row. Run it. Each cooking book shows the cooking average. Each technology book shows the technology average.
SELECT title,
genre,
price,
ROUND(AVG(price) OVER (PARTITION BY genre), 2) AS genre_avg
FROM books
WHERE genre IN ('Cooking', 'Technology');โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโ โ title โ genre โ price โ genre_avg โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโค โ The Quiet Kitchen โ Cooking โ 24.99 โ 24.82 โ โ Spice Roads โ Cooking โ 27.99 โ 24.82 โ โ One Pan Dinners โ Cooking โ 21.49 โ 24.82 โ โ Python for Curious Minds โ Technology โ 34.99 โ 32.32 โ โ SQL in a Weekend โ Technology โ 29.99 โ 32.32 โ โ Data for Everyone โ Technology โ 31.99 โ 32.32 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโ
Compare each row to its group
Now the useful part. Because each row still has its own price, we can compare it to the group. Price, minus the genre average. Run it. The Silent Orchard is one eighty eight above the mystery average. Murder at Mill Lane is two dollars thirteen below.
SELECT title,
price,
ROUND(price - AVG(price) OVER (PARTITION BY genre), 2) AS vs_genre
FROM books
WHERE genre = 'Mystery';โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโ โ title โ price โ vs_genre โ โโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโค โ The Silent Orchard โ 14.99 โ 1.88 โ โ The Last Lighthouse โ 13.99 โ 0.88 โ โ Murder at Mill Lane โ 10.99 โ -2.13 โ โ The Painted Door โ 12.49 โ -0.63 โ โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโ
Window versus GROUP BY
Think of a window as a frame you slide over the rows. Partition by decides which rows share a frame. The function looks through the frame, but the rows themselves stay where they are.
Summary
A window function adds over, and partition by sets the groups. Every row stays. Next, numbering rows.
Key takeaways- function OVER (...)
- PARTITION BY = the groups
- Rows are not squashed