Task
American Express wants a simple monthly performance report for a card portfolio. Write a SQL query against a single table to summarize monthly portfolio results.
Requirements
- Return one row per month for 2024 only.
- For each month, calculate:
month in YYYY-MM formattotal_revenue as the sum of revenuetotal_charge_off as the sum of charge_off_amountnet_performance as SUM(revenue) - SUM(charge_off_amount)
- Exclude rows where
statement_date is NULL.
- Order the results by month ascending.
Table Definition
amex_portfolio_monthly_activity
| Column | Type | Description |
|---|
| activity_id | INT | Unique row identifier |
| portfolio_name | VARCHAR(100) | Portfolio tracked in American Express reporting |
| statement_date | DATE | Statement or activity date |
| revenue | DECIMAL(12,2) | Revenue recognized for the row |
| charge_off_amount | DECIMAL(12,2) | Charge-off amount for the row |
| account_status | VARCHAR(20) | Status of the account segment |
Sample Data
| activity_id | portfolio_name | statement_date | revenue | charge_off_amount | account_status |
|---|
| 4 | Blue Cash Everyday | 2024-02-18 | 800.00 | 50.00 | Active |
| 1 | Platinum Card | 2024-01-15 | 1200.00 | 100.00 | Active |
| 7 | Delta SkyMiles Gold | 2024-03-10 | 0.00 | 200.00 | Delinquent |
| 10 | Blue Cash Preferred | 2023-12-31 | 500.00 | 20.00 | Active |
| 11 | Corporate Green | NULL | 900.00 | 30.00 | Active |
Expected Output
| month | total_revenue | total_charge_off | net_performance |
|---|
| 2024-01 | 2000.00 | 150.00 | 1850.00 |
| 2024-02 | 2200.00 | 125.00 | 2075.00 |
| 2024-03 | 1000.00 | 300.00 | 700.00 |
