SQL Occupancy Rate by Fare Class

Lesson 12.2 · Reading time: ~7 min · Practice tasks ↓

This lesson walks through the solution of the SQL task Flight occupancy by fare class on the Bookings database (PostgreSQL). You'll learn how to count all seats and occupied seats in one query, how to split the counts by fare class with FILTER, and why the answer needs two levels of aggregation: first per flight, then per airport.

The task: for each departure airport, calculate the average occupancy of business and economy class on flights that actually departed in August 2017. The occupancy of a flight is the percentage of occupied seats among all seats of that class. The result has three columns: departure_airport, average_business_occupancy and average_economy_occupancy, as percentages rounded to two decimals.

Try to solve the task yourself first; the full query is hidden at the end of the lesson.

Which Tables Are Needed

flights: the flight, its departure_airport, actual_departure and the aircraft (aircraft_code).
seats: all seats of each aircraft model with their class (fare_conditions).
boarding_passes: the seats actually taken on a flight (flight_id, seat_no).

The plan: for each flight, list all seats of its aircraft and mark which of them have a boarding pass.

Step-by-Step Solution

1. All Seats and Occupied Seats of Each Flight

Join every flight to the seats of its aircraft, then attach boarding passes with a LEFT JOIN, so empty seats stay in the result:

SELECT
    flights.flight_id,
    flights.departure_airport,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_seats,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_seats
FROM flights
JOIN seats USING (aircraft_code)
LEFT JOIN boarding_passes
    ON boarding_passes.seat_no = seats.seat_no
   AND boarding_passes.flight_id = flights.flight_id
WHERE flights.actual_departure BETWEEN '2017-08-01' AND '2017-09-01'
GROUP BY flights.flight_id, flights.departure_airport
ORDER BY flights.flight_id
LIMIT 3;

Result: for example, flight 2 from DME has 14 of 28 business seats and 87 of 142 economy seats occupied.

COUNT(seats.seat_no) counts all seats; COUNT(boarding_passes.seat_no) counts only seats with a boarding pass, because for empty seats the LEFT JOIN gives NULL.
With an inner JOIN to boarding_passes, empty seats would disappear, and every flight would look 100% full.
The boarding pass must match both the seat and the flight: the same seat number exists on every flight of that aircraft.
COUNT(...) FILTER (WHERE ...) counts only the rows of one class. In other databases, the same is written as COUNT(CASE WHEN ... THEN 1 END) (Lesson 4.4).

2. Occupancy of Each Flight

Divide the occupied seats by all seats. Be careful with the types: COUNT returns an integer, and in PostgreSQL an integer divided by an integer is an integer:

SELECT 3 / 12 AS integer_division, 3::numeric / 12 AS numeric_division;

Result: integer_division is 0, numeric_division is 0.25.

That's why the solution casts the seat counts to numeric. Some aircraft have no business class at all, for example the Bombardier CRJ-200 and the Cessna 208 Caravan. For them the number of business seats is 0, and the division must be skipped: CASE WHEN business_seats > 0 THEN ... END returns NULL, which AVG ignores.

3. Average per Airport

Put the per-flight calculation into a CTE and aggregate it again, now by departure_airport:

AVG(business_occupied / business_seats) * 100 is the average occupancy percentage;
::numeric(5, 2) rounds it to two decimals.

Result: 104 airports, from AAQ (82.83% business, 81.54% economy) onwards.

Average of Percentages vs. Overall Percentage

The task asks for the average of per-flight occupancy rates: each flight has the same weight, a nearly empty small plane counts as much as a full big one. The other possible metric is the overall occupancy: all occupied seats at the airport divided by all seats, SUM(occupied) / SUM(seats), where big planes weigh more. Both are valid, but they give different numbers, so read the task statement carefully. Here, the two-level aggregation is required exactly because the average is taken over flights.

Spoiler: the SQL query with the solution is hidden below. Click to reveal.


with occupancy as (
    select
        flights.flight_id, departure_airport,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Business') business_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Business'))::numeric business_seats,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Economy') economy_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Economy'))::numeric economy_seats
    from flights
    join seats using (aircraft_code)
    left join boarding_passes on
        boarding_passes.seat_no = seats.seat_no and
        boarding_passes.flight_id = flights.flight_id
    where flights.actual_departure between '2017-08-01' and '2017-09-01'
    group by flights.flight_id, departure_airport
)
select
    departure_airport,
    (avg(case when business_seats > 0 then business_occupancy / business_seats end) * 100)::numeric(5, 2) average_business_occupancy,
    (avg(case when economy_seats > 0 then economy_occupancy / economy_seats end) * 100)::numeric(5, 2) average_economy_occupancy
from occupancy
group by departure_airport
order by departure_airport;

Key Takeaways:

Count all seats from seats and occupied seats from boarding_passes attached with a LEFT JOIN; an inner join would hide empty seats.
COUNT(column) skips NULL, so it counts only matched rows; FILTER (WHERE ...) splits counts by category.
Integer division in PostgreSQL drops the fraction: cast to numeric before dividing.
Skip classes that an aircraft doesn't have with CASE WHEN seats > 0; AVG ignores NULL.
An average of per-flight rates needs two levels of aggregation: a CTE per flight, then GROUP BY airport.

Frequently Asked Questions

Why does my percentage calculation return 0 in SQL?

Because both operands are integers, and PostgreSQL, SQL Server and SQLite perform integer division: 3 / 12 = 0. Cast one operand to numeric or decimal, or multiply by 100.0 first. MySQL returns a decimal result for / and doesn't have this problem.

What does COUNT(...) FILTER (WHERE ...) do?

It counts only the rows that satisfy the condition, so several counts with different conditions fit in one GROUP BY. FILTER is supported by PostgreSQL and SQLite; in MySQL and SQL Server use COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN 1 ELSE 0 END).

When do I need two levels of aggregation?

When the metric is calculated per object and then averaged over groups: occupancy per flight, then the average per airport. One GROUP BY can't do both, so the first level goes into a CTE or a subquery.