SQL for Capex by Department | Dataford Interview Questions

Write a SQL query to find the total capital expenditure by department for the last fiscal quarter, joining a transactions table with a department mapping table.

Assume fiscal quarters follow calendar quarters, and determine the last fiscal quarter relative to current_date. Include only capital expenditure transactions with a matching department.

Output

One row per department, with department_name and total_capex.
Include departments with matching capital expenditure transactions only.
Sort by total_capex descending, then department_name ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
department_code	VARCHAR(10)	Code identifying the responsible department
transaction_date	DATE	Date the transaction was recorded
transaction_type	VARCHAR(20)	Financial transaction classification
amount	DECIMAL(14,2)	Transaction amount in dollars

department_mapping

Column	Type	Description
department_codePK	VARCHAR(10)	Unique department code
department_name	VARCHAR(100)	Readable department name

Your question is SQL for Capex by Department. 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