SQL Window Functions Experience | Dataford Interview Questions

What is your experience with SQL window functions?

Using the provided regions and yield_observations tables, demonstrate that experience by calculating each region's average yield for 2024 and its rank within its country. Include regions without a valid 2024 yield.

Output

One row per region, with country, region_name, average_yield, and country_rank.
Rank higher average yields first, share ranks for ties, place missing averages last, and sort by country, rank, and region name.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
country	VARCHAR(80)	Country containing the region
region_name	VARCHAR(120)	Name of the agricultural region

yield_observations

Column	Type	Description
observation_idPK	INT	Unique observation identifier
region_id	INT	Referenced region
observation_date	DATE	Date of the yield observation
yield_tonnes	DECIMAL(10,2)	Observed yield in tonnes

Your question is SQL Window Functions Experience. Start with the requirements and the two tables 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