Trip Occupancy Query

Problem

Given the tables below, write one query that returns, for every route, the number of trips it ran in the last 30 days, its average occupancy percentage (booked seats ÷ capacity, computed per trip then averaged), and a boolean underused for routes averaging below 40%. Cancelled bookings must not count. Routes with no trips in the window must still appear with trips = 0.

routes(id, name, city)
trips(id, route_id, capacity, departed_at)
bookings(id, trip_id, status)   -- status in ('confirmed', 'cancelled', 'no_show')

Examples

Example 1 — Route "Maadi → Smart Village" ran two trips: trip 10 (capacity 20, 15 confirmed, 2 cancelled) and trip 11 (capacity 20, 4 confirmed). Per-trip occupancy 75% and 20% → avg_occupancy = 47.5, underused = false.

Example 2 — Route "6th Oct → Zamalek" only has trips from 60 days ago → trips = 0, avg_occupancy = NULL, underused = false.

Constraints

• Postgres 14. no_show counts as a booked seat (the seat was taken)
• Round occupancy to 1 decimal place
• Order by avg_occupancy ascending, NULLs last

What they look for

The date filter in the join's ON clause or in a pre-filtered CTE (not the outer WHERE) so empty routes survive the LEFT JOIN, aggregating at trip level before averaging (averaging one ratio across all bookings skews toward busy trips), and guarding capacity 0. Follow-up: making it fast on 50M bookings — a partial index on bookings(trip_id) WHERE status <> 'cancelled' and partitioning trips by month.