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.
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)statement_date is NULL.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity row identifier |
| portfolio_name | VARCHAR(100) | American Express portfolio name |
| statement_date | DATE | Statement or activity date |
| revenue | DECIMAL(12,2) | Revenue recognized for the activity row |
| charge_off_amount | DECIMAL(12,2) | Charge-off amount associated with the activity row |
| account_status | VARCHAR(20) | Status of the account segment |
| 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 |
| 3 | Hilton Honors Surpass | 2024-02-05 | 1400.00 | 75.00 | Active |
| 6 | Gold Card | 2024-03-25 | 1000.00 | 100.00 | Active |
| 2 | Blue Cash Preferred | 2024-01-28 | 800.00 | 50.00 | Active |
| 11 | Corporate Green | 900.00 | 30.00 | Active | |
| 5 | Marriott Bonvoy Brilliant | 2024-02-28 | 0.00 | 0.00 | Closed |
| 8 | Cash Magnet | 2024-03-31 | 0.00 | 0.00 | |
| 9 | EveryDay Preferred | 2025-01-03 | 1500.00 | 60.00 | Active |
| 12 | Platinum Card | 2024-01-05 | 0.00 | 0.00 | Closed |
| 13 | Amazon Business Prime | 2024-07-06 | -41 | 160 | Charged Off |
| 14 | Hilton Honors Surpass | 2024-11-10 | 806 | 166 | Active |
| 15 | Amazon Business Prime | 2024-03-20 | 1449 | 39 | Active |
| 16 | Cash Magnet | 2024-02-21 | 1466 | 32 | Pending Closure |
| 17 | Delta SkyMiles Gold | 2024-06-29 | 1578 | 147 | Matured |
| 18 | Delta SkyMiles Gold | 2024-10-10 | -173 | 154 | Monitoring |
| 19 | Blue Cash Everyday | 2024-09-09 | 153 | 93 | null |
| 20 | Green Card | 2024-08-14 | 50 | 92 | Fraud Review |
| 21 | Blue Business Plus | null | 621 | 69 | Recovered |
| 22 | EveryDay Preferred | 2024-08-01 | 1242 | 158 | Collections |
| 23 | Amazon Business Prime | null | 1528 | 195 | Hardship |
| 24 | Gold Card | 2024-12-05 | 901 | 94 | Write-Off Review |
| 25 | Morgan Stanley Platinum | 2024-03-13 | 636 | 115 | Grace Period |
| 26 | Marriott Bonvoy Bevy | 2024-02-07 | 1441 | 130 | Suspended |
| 27 | Centurion Card | 2024-01-26 | 187 | 16 | Write-Off Review |
| month | total_revenue | total_charge_off | net_performance |
|---|---|---|---|
| 2024-01 | 2187.00 | 166.00 | 2021.00 |
| 2024-02 | 5107.00 | 287.00 | 4820.00 |
| 2024-03 | 3085.00 | 454.00 | 2631.00 |
| 2024-06 | 1578.00 | 147.00 | 1431.00 |
| 2024-07 | -41.00 | 160.00 | -201.00 |
| 2024-08 | 1292.00 | 250.00 | 1042.00 |
| 2024-09 | 153.00 | 93.00 | 60.00 |
| 2024-10 | -173.00 | 154.00 | -327.00 |
| 2024-11 | 806.00 | 166.00 | 640.00 |
| 2024-12 | 901.00 | 94.00 | 807.00 |
