Top Transactions with Dense Rank | Dataford Interview Questions

Your question is Top Transactions with Dense Rank. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

JPMorganChase risk analytics needs to review each customer's largest recent transactions. Write a PostgreSQL query that returns the top three transaction amounts per customer during the past 30 days, relative to CURRENT_DATE.

Requirements

Join transactions to customers to include the customer's name.
Filter transactions to the previous 30 days, including today, and exclude rows with a NULL amount.
Use DENSE_RANK() partitioned by customer and ordered by amount descending.
Return every transaction with a dense rank of 3 or less, including ties, ordered by customer and amount descending.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's full name

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	Customer associated with the transaction
transaction_timestamp	TIMESTAMP	Date and time when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
transaction_type	VARCHAR(30)	Transaction classification