Airline load factor, RASK and CASK: a route-level analysis in SQL

The building blocks

The identity RASK = PLF × yield is worth remembering: a revenue manager can lift unit revenue by filling seats or by charging more per passenger, and the two usually pull against each other.

Where the columns come from

Airline data sits in several systems. The fields you need are:

• Schedule / operations: flight_id, flight_date, route or origin-destination, aircraft_type, seats as configured for that departure (not the type's nominal count, if the airline flies more than one layout).
• Reference: distance_km per sector — use one agreed distance table (great-circle or the airline's own), not a mixture.
• Revenue accounting: pax (revenue passengers flown, excluding staff travel and infants without a seat), pax_revenue recognised for that flight.
• Cost allocation: operating_cost per flight, from finance's allocation of fuel, crew, airport, navigation, maintenance and ownership costs.

The cost allocation is the least objective column in the table. Ask finance which costs are included (direct only, or also overheads) before comparing routes.

The query (synthetic week, three routes)

Distances are rounded and illustrative, and every seat, passenger, revenue and cost figure is invented.

CREATE TABLE routes (route TEXT, distance_km INT);
CREATE TABLE flights (flight_id TEXT, route TEXT, seats INT, pax INT,
                      pax_revenue REAL, operating_cost REAL);

INSERT INTO routes VALUES ('DEL-BOM', 1140), ('DEL-JAI', 260), ('DEL-IXC', 240);

-- One synthetic week, 180-seat aircraft; revenue and cost in Rs
INSERT INTO flights VALUES
 ('F101','DEL-BOM',180,150, 870000, 820000),
 ('F102','DEL-BOM',180,138, 760000, 820000),
 ('F103','DEL-BOM',180,165, 990000, 820000),
 ('F201','DEL-JAI',180,168, 420000, 440000),
 ('F202','DEL-JAI',180,171, 430000, 440000),
 ('F301','DEL-IXC',180, 99, 300000, 420000);

WITH f AS (
  SELECT f.*, r.distance_km,
         f.seats * r.distance_km AS ask,
         f.pax   * r.distance_km AS rpk
  FROM flights f JOIN routes r USING (route))
SELECT route,
  COUNT(*) AS flights,
  ROUND(100.0 * SUM(rpk) / SUM(ask), 1)          AS plf_pct,
  ROUND(SUM(pax_revenue) / SUM(rpk), 2)          AS yield_rs,
  ROUND(SUM(pax_revenue) / SUM(ask), 2)          AS rask_rs,
  ROUND(SUM(operating_cost) / SUM(ask), 2)       AS cask_rs,
  ROUND(100.0 * (SUM(operating_cost) / SUM(ask)) / (SUM(pax_revenue) / SUM(rpk)), 1) AS belf_pct,
  ROUND((SUM(pax_revenue) - SUM(operating_cost)) / 100000.0, 2) AS result_lakh
FROM f GROUP BY route
UNION ALL
SELECT 'NETWORK', COUNT(*),
  ROUND(100.0 * SUM(rpk) / SUM(ask), 1),
  ROUND(SUM(pax_revenue) / SUM(rpk), 2),
  ROUND(SUM(pax_revenue) / SUM(ask), 2),
  ROUND(SUM(operating_cost) / SUM(ask), 2),
  ROUND(100.0 * (SUM(operating_cost) / SUM(ask)) / (SUM(pax_revenue) / SUM(rpk)), 1),
  ROUND((SUM(pax_revenue) - SUM(operating_cost)) / 100000.0, 2)
FROM f;

What the routes are saying

Delhi–Jaipur is nearly full and still loses money. At 94.2% PLF it has the best load factor in the network, but its short sector gives it a CASK of ₹9.40, and it needs 97.5% of seat-kilometres filled at current fares just to break even. That is close to impossible to sustain. The fix is on price or cost, not on selling more seats.

Delhi–Chandigarh is the opposite problem. Its yield is the highest (₹12.63 per passenger-km) because the fares on the one departure are high, but the flight was 55% full against a break-even of 77.0%. There is room for more passengers at lower fares, or for a smaller aircraft.

Delhi–Mumbai carries the network. Lowest yield, lowest CASK, and a PLF five points clear of break-even. The ₹1.6 lakh it earns covers the two losing routes and leaves ₹0.1 lakh for the week.

Averages that should not be taken

• Averaging flight load factors. The simple mean of the six flights' seat factors is 82.5%; the ASK-weighted network PLF is 83.5%. The mean gives a 240 km flight the same weight as a 1,140 km one. Always sum RPK and ASK first.
• Comparing CASK across different sector lengths. A short route's CASK will always look worse than a long route's. Compare routes of similar stage length, or adjust CASK to a standard sector length before ranking them.
• Seat factor versus load factor. Passengers ÷ seats (seat factor) and RPK ÷ ASK (load factor) are identical on a single sector but diverge across a network or on multi-leg flights. Say which one a report uses.
• Revenue by booking date. Revenue sold this week for flights next month belongs to next month. Use flown revenue for route results.
• Ancillary revenue. The query uses passenger revenue only. Baggage, seat selection and cargo change RASK; add them as separate columns so the passenger yield story stays readable.

How a network planning head wants to see it

A network or commercial head asks one question per route: grow, hold, fix or cut. Put PLF and break-even load factor side by side as a gap in percentage points (Mumbai +5.1, Chandigarh −22.0, Jaipur −3.3) with the weekly result beside it. Then give the lever for each loser: Jaipur needs yield or cost, Chandigarh needs passengers or a smaller gauge. A week of data is too short for a route decision, so present the same table on a rolling quarter before anyone changes a schedule.

For people moving from hotel or travel revenue management, the yield-versus-occupancy trade-off will be familiar; hotel management to revenue data analyst covers that move. The SQL here — weighted ratios and a network roll-up with UNION ALL — is part of the Data Analyst programme.