Task
You are given customer profile data and Card Member spend transactions from Amex Offers activity. Write a PostgreSQL query that compares Q1 2024 total spend across customer segments. Return one row per segment showing the number of active customers, total spend, average spend per active customer, and whether the segment is above or below the overall segment average total spend for the quarter. Only include transactions posted between 2024-01-01 and 2024-03-31, and treat customers as active if they have at least one qualifying transaction in that period.
Schema
customer_segments
| column | type | description |
|---|
| customer_id | INT | Unique Card Member identifier |
| segment_name | VARCHAR(50) | Customer segment label |
| enrollment_channel | VARCHAR(30) | Acquisition channel |
amex_cards
| column | type | description |
|---|
| card_id | INT | Unique card identifier |
| customer_id | INT | Card owner |
| card_product | VARCHAR(50) | Amex card product |
| open_date | DATE | Card open date |
card_transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction identifier |
| card_id | INT | Card used for the transaction |
| transaction_date | DATE | Posted transaction date |
| amount | DECIMAL(10,2) | Transaction amount |
| merchant_category | VARCHAR(40) | Merchant category |
Sample data
customer_segments
| customer_id | segment_name | enrollment_channel |
|---|
| 101 | Mass Affluent | Online |
| 102 | Premium | Branch |
| 103 | Small Business | Partner |
amex_cards
| card_id | customer_id | card_product | open_date |
|---|
| 1001 | 101 | Gold Card | 2023-06-15 |
| 1002 | 102 | Platinum Card | 2022-11-01 |
| 1003 | 103 | Business Gold Card | 2024-01-10 |
card_transactions
| transaction_id | card_id | transaction_date | amount | merchant_category |
|---|
| 1 | 1001 | 2024-01-05 | 120.50 | Dining |
| 2 | 1002 | 2024-02-10 | 900.00 | Travel |
| 3 | 1003 | 2024-03-12 | 450.00 | Advertising |
Expected output
| segment_name | active_customers | total_spend | avg_spend_per_active_customer | segment_vs_avg |
|---|
| Premium | 2 | 1900.00 | 950.00 | Above Average |
| Small Business | 2 | 1500.00 | 750.00 | Above Average |
| Mass Affluent | 2 | 851.00 | 425.50 | Below Average |
| Emerging Affluent | 1 | 300.00 | 300.00 | Below Average |
