Top Departments by Expenditure | Dataford Interview Questions

Write a query to find the top 5 departments by total expenditure in the last fiscal year at the University of Utah.

Use the fiscal year marked as the last completed fiscal year. Return only expenditure records associated with a department and that fiscal year.

Output

One row per department, with department_name and total_expenditure.
Include the five departments with the highest totals.
Order by total_expenditure descending, breaking ties by department_name ascending.

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(150)	University of Utah department name
college_name	VARCHAR(150)	College or administrative division containing the department

expenditures

Column	Type	Description
expenditure_idPK	INT	Unique expenditure record identifier
department_id	INT	Department charged for the expenditure
fiscal_year	INT	Fiscal year associated with the expenditure
amount	NUMERIC(14,2)	Expenditure amount in US dollars

fiscal_years

Column	Type	Description
fiscal_yearPK	INT	Fiscal year identifier
start_date	DATE	First date of the fiscal year
end_date	DATE	Last date of the fiscal year
is_last_completed	BOOLEAN	Whether this is the last completed fiscal year

Your question is Top Departments by Expenditure. 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.