Monthly Spend by Region and Category | Dataford Interview Questions

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-MM
- region
- travel_category
- total_spend
- booking_count
Exclude rows where spend_amount is NULL.
Order by booking_month, then region, then travel_category.

Schema

travel_bookings

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

Your question is Monthly Spend by Region and Category. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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-MM
- region
- travel_category
- total_spend
- booking_count
Exclude rows where spend_amount is NULL.
Order by booking_month, then region, then travel_category.

Schema

travel_bookings

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

Your question is Monthly Spend by Region and Category. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.