Top Projects by Region | Dataford Interview Questions

How would you write a query to find the top three most expensive projects in each region using a window function?

Use the provided regions, projects, and project_expenses tables. Treat a project’s total cost as the sum of its expense amounts, with projects having no expenses treated as zero.

Output

One row per selected project, with region_name, project_id, project_name, total_cost, and region_rank
Include up to three projects per region, ordered by total cost descending
Break cost ties by project_id ascending, and sort final results by region_name, then region_rank

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Region name

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(150)	Project name
region_id	INT	Region assigned to the project

project_expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
project_id	INT	Project associated with the expense
expense_amount	NUMERIC(14,2)	Expense amount recorded for the project

Your question is Top Projects by Region. Start with the requirements and the three 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