Top 3 Customers Per Month With Ties | Dataford Interview Questions

American Express wants a monthly view of its highest-spending cardmembers. Write a PostgreSQL query to find the top three spending customers by month during the most recent 12-month period.

Requirements

Include only posted purchase transactions made with active American Express cards.
Aggregate transaction amounts by customer and calendar month.
Use a ranking window function so ties at third place are included. The result may contain more than three customers in a month.
Return the month as YYYY-MM, customer details, total spending, and the customer rank.
Sort by month ascending, total spending descending, and customer ID ascending for deterministic output.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique cardmember identifier
customer_name	VARCHAR(100)	Cardmember name

cards

Column	Type	Description
card_idPK	INT	Unique American Express card identifier
customer_id	INT	Cardmember who owns the card
card_status	VARCHAR(20)	Current card status

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
card_id	INT	Card used for the transaction
transaction_ts	TIMESTAMP	Transaction timestamp
transaction_type	VARCHAR(20)	Transaction classification
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(12,2)	Transaction amount in dollars

Your question is Top 3 Customers Per Month With Ties. Start with the requirements and the three 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