Second-Highest Value With Window Functions | Dataford Interview Questions

Tiger Analytics is reviewing completed transaction activity for active clients. Write a PostgreSQL query to identify the second-highest transaction value, including every transaction tied at that rank.

Requirements

Join transactions with clients and consider only clients whose status is active.
Consider only transactions with transaction_status = 'completed' and a non-NULL transaction_value.
Use DENSE_RANK() to rank distinct transaction values from highest to lowest.
Return every transaction with rank 2, including ties, along with the client name and transaction value. Sort by transaction value descending and transaction ID ascending.

Schema

clients

Column	Type	Description
client_idPK	INT	Primary key for the client
client_name	VARCHAR(100)	Client name
segment	VARCHAR(30)	Client business segment
status	VARCHAR(20)	Client account status

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
client_id	INT	References clients.client_id
transaction_value	DECIMAL(12,2)	Monetary value of the transaction
transaction_status	VARCHAR(20)	Current transaction status
payment_method	VARCHAR(30)	Payment method used

Your question is Second-Highest Value With Window Functions. 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