Second-Highest Per Region Query | Dataford Interview Questions

Write a SQL query to find the second-highest transaction amount within each regional sales division for Lennar.

Use the provided division and transaction data. Treat the second-highest amount as the second distinct amount, and exclude transactions with a NULL amount.

Output

One row per division that has at least two distinct transaction amounts.
Columns: regional_division, second_highest_amount.
Sort by regional_division alphabetically. If multiple transactions share the qualifying amount, return one row for the division.

Schema

regional_sales_divisions

Column	Type	Description
division_idPK	INT	Unique identifier for the regional sales division
division_name	VARCHAR(100)	Name of the regional sales division

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the transaction
division_id	INT	Regional division associated with the transaction
transaction_amount	DECIMAL(12,2)	Amount recorded for the transaction
transaction_date	DATE	Date when the transaction occurred

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