Window Function for Second Rank | Dataford Interview Questions

American Express analysts need to identify customers with the second-highest annual spend within each market segment. Write a PostgreSQL query using DENSE_RANK() or RANK().

Requirements

Calculate each customer's total spend from qualifying 2024 transactions.
Join customers to transactions and group results by customer and market segment.
Rank customers within each market segment by total spend in descending order.
Return every customer tied for second place, ordered by market segment and customer ID. Ignore transactions with null, zero, or negative amounts, and exclude transactions outside 2024.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
market_segment	VARCHAR(50)	American Express customer market segment

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Transaction posting date
amount	NUMERIC(12,2)	Transaction amount in dollars

Your question is Window Function for Second Rank. 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