AirAsia — Route Profitability

AirAsia's network planning team wants to know which of five routes out of Kuala Lumpur is actually losing money, once fuel cost (which scales with distance) is netted against ticket revenue. You have three tables — routes, flights and bookings — at three different grains. Five sequential steps: revenue per flight, cost per flight, profit per flight (the join has a trap), profit normalized per kilometre flown, then the single route to cut.

Schema

▸airasia_routes

route_id	TEXT
origin	TEXT
destination	TEXT
distance_km	INT

▸airasia_flights

flight_id	SERIAL
route_id	TEXT
flight_date	DATE
seats_available	INT
fuel_cost	NUMERIC(10,2)
crew_cost	NUMERIC(10,2)
airport_fees	NUMERIC(10,2)

▸airasia_bookings

booking_id	SERIAL
flight_id	INT
fare_amount	NUMERIC(10,2)

5 steps

1Total revenue per flight
2Total operating cost per flight
3Profit per flight, without fanning out the cost
4Normalize profit by distance flown
5Name the one route to cut

Basic

This is a Basic take-home

Multi-step case rounds — several questions on one shared dataset — unlock with a Basic plan.

See Basic plans

RM 25/mo · cancel anytime

Company names are trademarks of their respective owners, used here only to describe practice material. mahir_data is not affiliated with, endorsed by, or sponsored by any company named on this site. Terms.