SQL Hospital Cost Drivers | Dataford Interview Questions

Your question is SQL Hospital Cost Drivers. Start with the requirements and the two tables on the right.

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

Problem

Huron Consulting Group is reviewing hospital spending for a client engagement. Write a PostgreSQL query that analyzes 2025 expenses by hospital department and identifies each department's largest cost driver.

Requirements

Include every department, including departments with no qualifying 2025 expenses.
Aggregate spending by department and expense category, treating NULL amounts as zero and NULL categories as Unclassified.
Use a window function to rank cost categories within each department and return the largest driver. Return tied largest categories when applicable.
Order the final results by total spending descending, then department name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique hospital department identifier
department_name	VARCHAR(100)	Hospital department name
cost_center	VARCHAR(30)	Financial cost center code

expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
department_id	INT	Department associated with the expense
expense_category	VARCHAR(80)	Expense classification
expense_date	DATE	Date the expense was incurred
amount	NUMERIC(12,2)	Expense amount in dollars
vendor	VARCHAR(100)	Expense vendor