SQL Top Spending by Month | Dataford Interview Questions

Booz Allen Hamilton's Data Science practice needs a monthly view of spending by department. Write a PostgreSQL query that returns the three highest-spending departments for each calendar month.

Requirements

Include only transactions with status = 'completed' and a positive amount.
Join transactions to departments so orphaned department IDs are excluded.
Aggregate spending by calendar month and department, then rank departments independently within each month.
Return the top three departments per month, ordered by month ascending and spending descending. Break equal-spending ties alphabetically by department name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
department_id	INT	Department associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount in dollars
status	VARCHAR(20)	Transaction processing status

Your question is SQL Top Spending by Month. 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