Top Departments by Quarter | Dataford Interview Questions

Write a query to find the top three departments by spending in each fiscal quarter.

Use the fiscal quarter definitions provided in fiscal_quarters and include only posted expenditures. Return quarters that have at least one posted expenditure.

Output

One row per department selected for each fiscal year and fiscal quarter.
Columns: fiscal_year, fiscal_quarter, department_name, total_spending, and spending_rank.
Include the three highest-spending departments per quarter, breaking ties alphabetically by department name. Order by fiscal year, quarter, rank, and department name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_code	VARCHAR(10)	Short department code
department_name	VARCHAR(100)	City department name

fiscal_quarters

Column	Type	Description
fiscal_yearPK	INT	Fiscal year containing the quarter
fiscal_quarterPK	INT	Fiscal quarter number from 1 through 4
start_date	DATE	Inclusive start date of the fiscal quarter
end_date	DATE	Inclusive end date of the fiscal quarter

expenditures

Column	Type	Description
expenditure_idPK	INT	Unique expenditure identifier
department_id	INT	Department responsible for the expenditure
expenditure_date	DATE	Date the expenditure was recorded
amount	NUMERIC(12,2)	Expenditure amount in US dollars
status	VARCHAR(20)	Expenditure processing status

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