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.
travel_bookings table.booking_date is in March 2024.region, and travel_category.booking_month formatted as YYYY-MMregiontravel_categorytotal_spendbooking_countspend_amount is NULL.booking_month, then region, then travel_category.| 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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| booking_idPK | 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) | Travel category such as Air, Hotel, or Rail |
| spend_amount | DECIMAL(10,2) | Total booking spend |
| 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 | |
| 1006 | James Park | 2024-03-19 | APAC | Air | 500.00 |
| 1008 | Ava Thompson | 2024-04-02 | North America | Air | 200.00 |
| 1 | Maya Chen | 2024-03-21 | null | International Air | 868 |
| 2 | Daniel Reed | 2024-04-03 | Mexico | Car | 753 |
| 3 | Ava Thompson | 2024-03-22 | France | Economy Cabin | 833 |
| 4 | Ethan Walker | 2024-04-05 | Benelux | Premium Cabin | -14 |
| 5 | Isabella Cruz | 2024-03-07 | EMEA | Managed Hotel | 642 |
| 6 | Arjun Mehta | 2024-03-19 | Greater China | Travel Insurance | 310 |
| 7 | Anika Sharma | 2024-03-28 | Canada | Crew Travel | -8 |
| 8 | Sara Ahmed | 2024-03-08 | Latin America | Air | 171 |
| 9 | Priya Nair | 2024-02-28 | Europe | Airport Transfer | 178 |
| 10 | James Park | 2024-03-23 | Africa | Long Stay | 805 |
| 11 | Fatima Ali | 2024-04-03 | Greater China | Hotel | 94 |
| 12 | James Park | 2024-03-13 | Benelux | Airport Transfer | -57 |
| 13 | Sofia Patel | 2024-03-29 | Middle East | Managed Hotel | 752 |
| 14 | Benjamin Cole | 2024-03-11 | Mexico | null | 448 |
| 15 | Elena Rossi | 2024-03-24 | Central Europe | Rail | 758 |
| booking_month | region | travel_category | total_spend | booking_count |
|---|---|---|---|---|
| 2024-03 | Africa | Long Stay | 805.00 | 1 |
| 2024-03 | APAC | Air | 500.00 | 1 |
| 2024-03 | APAC | Rail | 90.00 | 1 |
| 2024-03 | Benelux | Airport Transfer | -57.00 | 1 |
| 2024-03 | Canada | Crew Travel | -8.00 | 1 |
| 2024-03 | Central Europe | Rail | 758.00 | 1 |
| 2024-03 | EMEA | Air | 610.00 | 1 |
| 2024-03 | EMEA | Hotel | 420.00 | 1 |
| 2024-03 | EMEA | Managed Hotel | 642.00 | 1 |
| 2024-03 | France | Economy Cabin | 833.00 | 1 |
| 2024-03 | Greater China | Travel Insurance | 310.00 | 1 |
| 2024-03 | Latin America | Air | 171.00 | 1 |
| 2024-03 | Mexico | null | 448.00 | 1 |
| 2024-03 | Middle East | Managed Hotel | 752.00 | 1 |
| 2024-03 | North America | Air | 1000.00 | 2 |
| 2024-03 | North America | Hotel | 300.00 | 1 |
| 2024-03 | null | International Air | 868.00 | 1 |
