Data Analyst Interview Questions & Answers: Set 14, Airline & Travel

SQL

1. 1.Find the on-time departure percentage per route per month.

   Hide answer

   Answer: Count departed flights per route and month, and divide the on-time ones by the total. A common definition is departure within 15 minutes of schedule. Assume flights(flight_id, route_id, sched_dep, actual_dep, status). Exclude cancelled flights, because they have no actual departure. Multiply by 100.0 rather than 100 so integer division does not truncate the result to 0.

   SELECT route_id,
          DATE_TRUNC('month', sched_dep) AS month,
          COUNT(*) AS flights,
          ROUND(100.0 * COUNT(*) FILTER (
                WHERE actual_dep <= sched_dep + INTERVAL '15 minutes')
                / COUNT(*), 1) AS on_time_pct
   FROM flights
   WHERE status <> 'Cancelled'
   GROUP BY route_id, DATE_TRUNC('month', sched_dep)
   ORDER BY route_id, month;

2. 2.Calculate the seat occupancy (load factor) per flight.

   Hide answer

   Answer: Load factor = passengers flown / seats available x 100. Join flights to aircraft for seat capacity and count boarded passengers from bookings. Assume flights(flight_id, aircraft_id), aircraft(aircraft_id, seat_capacity) and bookings(flight_id, status). Use a LEFT JOIN so flights with zero passengers still show 0%. The trap is counting cancelled or no-show bookings as occupied seats.

   SELECT f.flight_id,
          a.seat_capacity,
          COUNT(b.booking_id) AS pax_flown,
          ROUND(100.0 * COUNT(b.booking_id) / a.seat_capacity, 1)
            AS load_factor_pct
   FROM flights f
   JOIN aircraft a ON a.aircraft_id = f.aircraft_id
   LEFT JOIN bookings b
          ON b.flight_id = f.flight_id
         AND b.status = 'Flown'
   GROUP BY f.flight_id, a.seat_capacity;