Your question is Window Functions for MoM Change. 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.
Capital One's card analytics team wants to monitor monthly spending volume per cardholder across its credit card portfolio. Write a PostgreSQL query that calculates the month-over-month change in the average monthly transaction volume.
Assume transaction volume means the sum of posted purchase amounts for each cardholder during a calendar month. Include only cardholders with at least one qualifying transaction in that month. Exclude pending transactions, refunds, and transactions with a NULL amount.
LAG to calculate the month-over-month percentage change, returning NULL for the first month.| Column | Type | Description |
|---|---|---|
| cardholder_idPK | INTEGER | Unique cardholder identifier |
| cardholder_name | VARCHAR(100) | Cardholder's full name |
| segment | VARCHAR(30) | Customer portfolio segment |
| Column | Type | Description |
|---|---|---|
| card_idPK | INTEGER | Unique card identifier |
| cardholder_id | INTEGER | References cardholders.cardholder_id |
| card_status | VARCHAR(20) | Current card status |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| card_id | INTEGER | References cards.card_id |
| transaction_ts | TIMESTAMP | Transaction timestamp |
| transaction_type | VARCHAR(20) | Purchase or refund |
| status | VARCHAR(20) | Transaction processing status |
| amount | NUMERIC(12,2) | Transaction amount in dollars |