Practice · Agriculture · Hard

Average of yearly totals (subquery in FROM)

Return one number, avg_total (2 decimals): the average across years of each year’s total crop output.

Runs in your browser. No signup needed.

What you are given

Aggregate a derived table: SELECT AVG(t) FROM (SELECT year, SUM(million_tonnes) AS t FROM crops GROUP BY year). This is “aggregate an aggregate”.

SELECT ROUND(AVG(t), 2) AS avg_total
FROM (
  SELECT year, SUM(million_tonnes) AS t FROM crops GROUP BY year
);

The reference solution is checked automatically when you run your query on the practice page — results are compared row by row.

A subquery in the FROM clause is just a table you built on the fly — you can query it like any other. (SQLite docs)