You are given transaction and customer data from American Express cardmembers. Write a PostgreSQL query to compare June 2024 performance across regions and customer segments. Return one row per region and segment, including the number of active cardmembers, total billed amount, average billed amount per active cardmember, and a performance label showing whether that group is Above Portfolio Average or Below Portfolio Average based on average billed amount per active cardmember for the month. Only include groups with at least 2 active cardmembers in June 2024.
amex_customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique cardmember ID |
| customer_name | VARCHAR(100) | Cardmember name |
| region | VARCHAR(50) | Customer region |
| segment | VARCHAR(50) | Customer segment |
| signup_date | DATE | Date the customer joined |
amex_transactions| column | type | description |
|---|---|---|
| transaction_id | INT | Unique transaction ID |
| customer_id | INT | Cardmember ID |
| transaction_date | DATE | Transaction date |
| billed_amount | DECIMAL(10,2) | Posted billed amount |
| merchant_category | VARCHAR(50) | Merchant category |
amex_customers: (101, 'Ava Patel', 'East', 'Consumer', '2023-11-15'), (102, 'Brian Lee', 'East', 'Small Business', '2024-01-10'), (103, 'Carla Gomez', 'West', 'Consumer', '2024-02-20')
amex_transactions: (1001, 101, '2024-06-03', 120.00, 'Travel'), (1002, 101, '2024-06-18', 80.00, 'Dining'), (1003, 102, '2024-06-07', 500.00, 'Office Supplies')
| region | segment | active_cardmembers | total_billed_amount | avg_billed_per_active_cardmember | performance_vs_portfolio |
|---|---|---|---|---|---|
| East | Consumer | 2 | 500.00 | 250.00 | Below Portfolio Average |
| West | Consumer | 2 | 850.00 | 425.00 | Above Portfolio Average |