SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 127 of 250

Easy

Show the cheapest price alongside each album

Your task

Return title, price, and the lowest price in that album’s genre as genre_min_price. Sort by album ID.

Partitioning by genre limits a window function to other albums in the same genre.

Sample data

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

Expected output

titlepricegenre_min_price
Night Bus1816
Static Bloom2219
Soft Circuit2424
Blue Hour1616
Coastline1919
Low Tide2121
Show a hint

Apply MIN(price) OVER (PARTITION BY genre).

Window functions guide

Keep detail rows while summarizing

GROUP BY would return one row per genre. A window aggregate with PARTITION BY leaves every album in the result and gives each row its genre's average. This makes it easy to compare a row with its peers.

SELECT title, genre, price,
       AVG(price) OVER (PARTITION BY genre) AS genre_average
FROM albums;

Specify the window order and frame

The ORDER BY inside OVER defines the order of calculation. ROWS defines which neighboring rows contribute. This example adds the current sale to all earlier sales by ID. The last ORDER BY controls the order shown on screen.

SELECT id, quantity,
       SUM(quantity) OVER (
         ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_quantity
FROM sales
ORDER BY id;

Rank first, then filter

Window results are calculated too late for the same SELECT's WHERE clause. Compute a rank in a CTE, then filter the named result. ROW_NUMBER assigns one position per row. RANK gives ties the same position and may leave gaps.

WITH ranked AS (
  SELECT title, genre, ROW_NUMBER() OVER (
    PARTITION BY genre ORDER BY price, id
  ) AS position
  FROM albums
)
SELECT genre, title FROM ranked WHERE position = 1;
SQLiteCtrl / ⌘ + Enter

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

Results

Loading SQLite…