SQL Challenger
All SQL guides

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

titlegenrestockgenre_avg_stockstock_difference
Low TideAmbient220
Soft CircuitElectronic000
CoastlineIndie862
Static BloomIndie46-2
Blue HourJazz35-2
Night BusJazz752
Try a related challenge