Average Onboard Spend by Itinerary | Dataford Interview Questions

Write a SQL query using a JOIN and an aggregate function to calculate the average onboard spend per guest, grouped by cruise itinerary at Carnival Cruise Line.

Use the provided itinerary and guest spending data. Include only guests with a recorded onboard spend.

Output

One row per itinerary with recorded guest spending.
Return itinerary_name and average_onboard_spend.
Round the average to two decimal places and sort from highest average to lowest, breaking ties alphabetically by itinerary name.

Schema

cruise_itineraries

Column	Type	Description
itinerary_idPK	INT	Unique identifier for the cruise itinerary
itinerary_name	VARCHAR(100)	Carnival cruise itinerary name

guest_onboard_spend

Column	Type	Description
spend_idPK	INT	Unique identifier for the guest spending record
guest_id	INT	Identifier for the guest
itinerary_id	INT	Itinerary associated with the guest spending record
onboard_spend	DECIMAL(10,2)	Recorded onboard spend for the guest

Your question is Average Onboard Spend by Itinerary. Start with the requirements and the two tables 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