Second-Highest Per Department | Dataford Interview Questions

Write a query to find the second-highest financial transaction for each department using window functions.

Return all transactions tied at the second-highest distinct amount. Exclude departments with fewer than two non-null transaction amounts.

Output

One row per qualifying transaction.
Columns: department_name, transaction_id, transaction_amount, and transaction_rank.
Order by department_name, transaction_amount descending, then transaction_id ascending.

departments

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

financial_transactions

Column	Type	Description
transaction_idPK	INT	Unique financial transaction identifier
department_id	INT	Department associated with the transaction
amount	NUMERIC(12,2)	Financial transaction amount

Your question is Second-Highest Per 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.