SQL window functions with examples
Show each album alongside average stock for its genre with AVG and PARTITION BY.
Sample query
SELECT title, genre, stock,
ROUND(AVG(stock) OVER (PARTITION BY genre), 1) AS genre_avg_stock,
ROUND(stock - AVG(stock) OVER (PARTITION BY genre), 1) AS stock_difference
FROM albums
ORDER BY genre, title;Keep one row per album
The shop wants to see which albums stand above or below their genre’s typical stock. The title needs to stay in the result because staff act on individual albums. An AVG with GROUP BY genre would give one summary row per genre. AVG with OVER adds that average alongside each album.
Partition the rows by genre
PARTITION BY genre gives each album an average computed from its own genre. Jazz has seven copies of Night Bus and three copies of Blue Hour, so both rows show an average of five. Their differences are two and minus two respectively.
Sort the output separately
The final ORDER BY controls how the result is displayed. The window uses the full genre partition here, so sorting the output by stock instead would leave the computed averages unchanged. Ambient has one album, so its stock is also its genre average and its difference is zero.
Query result
| title | genre | stock | genre_avg_stock | stock_difference |
|---|---|---|---|---|
| Low Tide | Ambient | 2 | 2 | 0 |
| Soft Circuit | Electronic | 0 | 0 | 0 |
| Coastline | Indie | 8 | 6 | 2 |
| Static Bloom | Indie | 4 | 6 | -2 |
| Blue Hour | Jazz | 3 | 5 | -2 |
| Night Bus | Jazz | 7 | 5 | 2 |
