Allowed R6 · Within policy
{
"slug": "trip-occupancy-sql",
"title": "Trip Occupancy Query",
"difficulty": "medium",
"tags": [
"sql",
"technical",
"backend",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 1,
"last_reported_at": "2026-08-20T11:55:03.203701Z",
"url": "https://www.mokabla.com/en/c/swvl/trip-occupancy-sql",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/swvl/trip-occupancy-sql",
"question": "https://www.mokabla.com/agent/_call/get_question?company=swvl&question=trip-occupancy-sql"
},
"company": {
"slug": "swvl",
"name_en": "Swvl"
},
"body_md": "## Problem\nGiven the tables below, write one query that returns, for every route, the number of trips it ran in the\nlast 30 days, its average occupancy percentage (booked seats ÷ capacity, computed per trip then averaged),\nand a boolean `underused` for routes averaging below 40%. Cancelled bookings must not count. Routes with\nno trips in the window must still appear with `trips = 0`.\n\n```\nroutes(id, name, city)\ntrips(id, route_id, capacity, departed_at)\nbookings(id, trip_id, status) -- status in ('confirmed', 'cancelled', 'no_show')\n```\n\n## Examples\n**Example 1**, Route \"Maadi → Smart Village\" ran two trips: trip 10 (capacity 20, 15 confirmed,\n2 cancelled) and trip 11 (capacity 20, 4 confirmed). Per-trip occupancy 75% and 20% → `avg_occupancy =\n47.5`, `underused = false`.\n\n**Example 2**, Route \"6th Oct → Zamalek\" only has trips from 60 days ago → `trips = 0`,\n`avg_occupancy = NULL`, `underused = false`.\n\n## Constraints\n- Postgres 14. `no_show` counts as a booked seat (the seat was taken)\n- Round occupancy to 1 decimal place\n- Order by `avg_occupancy` ascending, NULLs last\n\n## What they look for\nThe date filter in the join's `ON` clause or in a pre-filtered CTE (not the outer `WHERE`) so empty routes\nsurvive the LEFT JOIN, aggregating at trip level before averaging (averaging one ratio across all bookings\nskews toward busy trips), and guarding capacity 0. Follow-up: making it fast on 50M bookings, a partial\nindex on `bookings(trip_id) WHERE status <> 'cancelled'` and partitioning `trips` by month.",
"follow_ups_md": "- Same report but per route per weekday.\n- How would you compute this incrementally every hour instead of scanning 30 days?",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}