Your question is Top 3 Transactions Per Customer. Start with the requirements and the three 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.
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.
transaction_status = 'POSTED', a positive non-null amount, and an OPEN account.transaction_timestamp using PostgreSQL date functions.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique Citi customer identifier |
| customer_name | VARCHAR(100) | Customer display name |
| 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 |
| 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 |