Second-Highest Claims Amount | Dataford Interview Questions

Write a query to find the second-highest transaction amount from a claims database.

Use the claims and claim_transactions tables. Treat the second-highest amount as the second-highest distinct non-null value, even when multiple transactions share an amount.

Output

Return one row with the column second_highest_transaction_amount.
Return only the second-highest distinct non-null transaction amount.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique identifier for the claim
claim_number	VARCHAR(20)	External claim reference number
payer_name	VARCHAR(100)	Name of the healthcare payer
claim_status	VARCHAR(20)	Current processing status of the claim

claim_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the transaction
claim_id	INT	Claim associated with the transaction
transaction_amount	NUMERIC(12,2)	Amount recorded for the transaction
transaction_type	VARCHAR(30)	Type of claims payment transaction

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