Top Credit Products Rolling 30 Days | Dataford Interview Questions

Argus Information & Advisory Services needs a daily view of which credit card products generate the highest settled transaction volume. Write a PostgreSQL query that calculates and ranks product volume for each reporting date.

Requirements

Sum settled transaction amounts for each product during the inclusive rolling window ending on each report_date. Include transactions newer than report_date - INTERVAL '30 days' and on or before the report date.
Treat products with no qualifying transactions as having volume 0.
Rank products separately for each report date and return only the top three.
Order by report date ascending, volume descending, and product ID ascending to make ties deterministic.

Schema

products

Column	Type	Description
product_idPK	INT	Unique credit card product identifier
product_name	VARCHAR(100)	Credit card product name

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
product_id	INT	Credit card product used for the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
merchant_category	VARCHAR(40)	Merchant category associated with the transaction

report_dates

Column	Type	Description
report_datePK	DATE	Date for a rolling-volume snapshot

Your question is Top Credit Products Rolling 30 Days. 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