Second-Highest Per Group | Dataford Interview Questions

How would you write a query to find the second-highest transaction value for each client department using window functions?

Use the provided department and transaction data. Treat the second-highest value as the second-highest distinct non-null transaction value.

Output

Return one row per department with a second-highest value.
Columns: department_name, second_highest_transaction_value.
Exclude departments without a second-highest distinct value and order by department_name ascending.

departments

Column	Type	Description
department_idPK	INT	Unique identifier for a client department
department_name	VARCHAR(100)	Name of the client department

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for a transaction
department_id	INT	Department associated with the transaction
transaction_value	DECIMAL(12,2)	Monetary value of the transaction

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