SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 53 of 250

Moderate

Compare price ranges across genres

Your task

Return genre, album_count, and price_spread for genres with at least two albums. Sort by price_spread descending, then genre.

Subtracting the minimum from the maximum gives a group’s price spread.

Sample data

Table:albums
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

genrealbum_countprice_spread
Indie23
Jazz22
Show a hint

Use MAX(price) - MIN(price) and filter groups with HAVING.

Aggregates and reporting guide

Set the grain of a summary

Without GROUP BY, aggregates summarize the entire input. With GROUP BY genre, each result row represents one genre. Include a group key or an aggregate for each selected value so the meaning of the result is clear.

SELECT genre, COUNT(*) AS albums,
       MIN(price) AS lowest_price,
       MAX(price) AS highest_price
FROM albums
GROUP BY genre;

Filter rows or filter groups

WHERE runs before grouping. HAVING tests the resulting groups. Here sold-out albums cannot contribute to the counts or averages. Genres with fewer than two qualifying albums are dropped after grouping.

SELECT genre, COUNT(*) AS in_stock, AVG(price) AS average_price
FROM albums
WHERE stock > 0
GROUP BY genre
HAVING COUNT(*) >= 2;

Count records at the right level

A LEFT JOIN leaves one row with a NULL sale ID for each unsold album. COUNT(*) would count that placeholder. COUNT(s.id) skips it and correctly shows zero sales for those albums.

SELECT a.title, COUNT(s.id) AS sale_records
FROM albums AS a
LEFT JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…