Practice · Agriculture · Hard

Above-average harvest years (CTE)

Using a CTE that totals million_tonnes per year, return the year and total (2 decimals) for years whose total is above the average yearly total, oldest first.

Solve this challenge →

Runs in your browser. No signup needed.

What you are given

Build WITH yr AS (SELECT year, SUM(million_tonnes) AS t FROM crops GROUP BY year), then filter yr WHERE t > (SELECT AVG(t) FROM yr).

Starting point

WITH yr AS (
  SELECT year, SUM(million_tonnes) AS t FROM crops GROUP BY year
)
SELECT year, ROUND(t, 2) AS total
FROM yr
WHERE t > (SELECT AVG(t) FROM yr)
ORDER BY year;

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

A CTE (WITH …) names a temporary result so the rest of the query can read cleanly — the readable alternative to nested subqueries. (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
Each crop’s best year (correlated) Hard

Open it in the playground →