Top 3 Transactions Per Customer

MediumSQL · PostgreSQL

Problem

Citi's data science team needs a monthly view of each customer's highest-value posted card transactions. Write a PostgreSQL query using CTEs and a window function to rank transactions within each customer-month group.

Requirements

Join customers, accounts, and transactions.
Include only transactions with transaction_status = 'POSTED', a positive non-null amount, and an OPEN account.
Derive the month from transaction_timestamp using PostgreSQL date functions.
Return the top three transactions per customer per month, ranking by amount descending, then timestamp and transaction ID descending to break ties.
Return customers with fewer than three qualifying transactions for a month, and order the final output by customer, month, and rank.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Citi customer identifier
customer_name	VARCHAR(100)	Customer display name

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
customer_id	INT	Customer who owns the account
account_type	VARCHAR(30)	Account product type
account_status	VARCHAR(20)	Current account status

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique card transaction identifier
account_id	INT	Account charged by the transaction
transaction_timestamp	TIMESTAMP	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount in dollars
transaction_status	VARCHAR(20)	Processing status of the transaction
merchant_category	VARCHAR(50)	Merchant category classification

Tablescustomersaccountscard_transactions