Practice · Agriculture · Hard

Latest year per crop (ROW_NUMBER + PARTITION)

For each crop, number its rows from the most recent year (rn = 1) to oldest, using a partitioned window. Return crop, year, million_tonnes and rn, ordered by crop then rn.

Solve this challenge →

Runs in your browser. No signup needed.

What you are given

ROW_NUMBER() OVER (PARTITION BY crop ORDER BY year DESC). PARTITION BY restarts the numbering per crop.

Starting point

SELECT crop, year, million_tonnes,
       ROW_NUMBER() OVER (PARTITION BY crop ORDER BY year DESC) AS rn
FROM crops
ORDER BY crop, rn;

The reference solution is checked automatically when you run your query on the practice page — results are compared row by row, and order matters for this one.

Did you know

PARTITION BY is “GROUP BY that keeps the rows” — you get a number per group without collapsing the detail. (SQLite docs)

More Agriculture challenges

Which crops do we track? Easy
Total wheat on record Easy
Average output per crop Medium
Total staple output per year Medium
Above-average harvest years (CTE) Hard

Open it in the playground →