Second-Highest Per Customer | Dataford Interview Questions

Write a SQL query using window functions to find the second-highest transaction amount for each customer within a specific month.

Use May 2024 as the target month. Treat the second-highest amount as the second distinct amount, exclude NULL amounts, and exclude customers without at least two distinct transactions in the month.

Output

One row per qualifying customer
Columns: customer_id, second_highest_amount
Order by customer_id ascending

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Identifier of the customer making the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	DECIMAL(12,2)	Transaction amount

Your question is Second-Highest Per Customer. Start with the requirements and the one table 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