Rolling Crop Yield by Region | Dataford Interview Questions

Write a SQL query to calculate the rolling average of crop yields over a three-year period for each unique farm region.

Use the crop_yields table. First determine each region's average yield for each year, then calculate the rolling average for the current year and two preceding available years.

Output

One row per farm region and year
Columns: farm_region, yield_year, annual_average_yield, and rolling_3_year_average
Include all regions and years, including rows where yield values are missing
Order by farm_region, then yield_year

Schema

crop_yields

Column	Type	Description
yield_idPK	INT	Unique yield observation identifier
farm_region	VARCHAR(100)	Farm region where the yield was recorded
yield_year	INT	Calendar year of the yield observation
yield_tons_per_hectare	NUMERIC(10,2)	Crop yield measured in tons per hectare

Your question is Rolling Crop Yield by Region. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem