Task
American Express Global Business Travel wants a clean monthly summary from raw Egencia booking records. Write a SQL query that summarizes total booking spend by month, region, and travel category.
Requirements
- Use only the
travel_bookings table.
- Include only rows where
booking_date is in March 2024.
- Group results by month,
region, and travel_category.
- Return these columns:
booking_month formatted as YYYY-MMregiontravel_categorytotal_spendbooking_count
- Exclude rows where
spend_amount is NULL.
- Order by
booking_month, then region, then travel_category.
Table Definition
| column | type | description |
|---|
| booking_id | INT | Unique booking record ID |
| traveler_name | VARCHAR(100) | Traveler full name |
| booking_date | DATE | Date the booking was created |
| region | VARCHAR(50) | Geographic region for the booking |
| travel_category | VARCHAR(50) | Booking category such as Air, Hotel, or Rail |
| spend_amount | DECIMAL(10,2) | Total booking spend |
Sample Data
| booking_id | traveler_name | booking_date | region | travel_category | spend_amount |
|---|
| 1004 | Priya Nair | 2024-03-12 | EMEA | Hotel | 420.00 |
| 1001 | Maya Chen | 2024-03-03 | North America | Air | 850.00 |
| 1007 | Daniel Reed | 2024-03-21 | APAC | Rail | 90.00 |
| 1002 | Omar Hassan | 2024-03-08 | North America | Hotel | 300.00 |
| 1009 | Elena Rossi | 2024-03-25 | EMEA | Air | 610.00 |
| 1005 | Lucas Meyer | 2024-02-28 | EMEA | Air | 700.00 |
| 1003 | Sofia Patel | 2024-03-15 | North America | Air | 150.00 |
| 1010 | Nina Brooks | 2024-03-28 | APAC | Hotel | NULL |
| | | | | |
Expected Output
| booking_month | region | travel_category | total_spend | booking_count |
|---|
| 2024-03 | APAC | Air | 500.00 | 1 |
| 2024-03 | APAC | Rail | 90.00 | 1 |
| 2024-03 | EMEA | Air | 610.00 | 1 |
| 2024-03 | EMEA | Hotel | 420.00 | 1 |
| 2024-03 | North America | Air | 1000.00 | 2 |
| 2024-03 | North America | Hotel | 300.00 | 1 |
