Flight Seat Occupancy and Wait Queue Summary

Easy

An airline analytics team needs a compact load report for every scheduled flight. Table: Flights flight_id: unique identifier of a flight capacity: maximum number of passengers that can be seated Table: Passengers passenger_id: unique booking request identifier flight_id: flight selected by the passenger Seat allocation model: For a flight with passenger_count requests: booked_cnt = min(capacity, passenger_count) waitlist_cnt = max(passenger_count - capacity, 0) Task: Return one row per flight with booked and waitlist totals. Output requirements: Return exactly these columns in this order: flight_id booked_cnt waitlist_cnt Include flights that have no passenger rows. Return rows ordered by flight_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 1         | 2        |
| 2         | 2        |
| 3         | 1        |

Passengers:
| passenger_id | flight_id |
|--------------|-----------|
| 101          | 1         |
| 102          | 1         |
| 103          | 1         |
| 104          | 2         |
| 105          | 2         |
| 106          | 3         |
| 107          | 3         |

Output

[
  {"flight_id":1,"booked_cnt":2,"waitlist_cnt":1},
  {"flight_id":2,"booked_cnt":2,"waitlist_cnt":0},
  {"flight_id":3,"booked_cnt":1,"waitlist_cnt":1}
]

Explanation

Flight 1 has three requests for two seats, so one request is waitlisted. Flight 2 exactly fits capacity. Flight 3 has one extra request beyond capacity.

Example 2

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 10        | 3        |
| 11        | 1        |
| 12        | 4        |

Passengers:
| passenger_id | flight_id |
|--------------|-----------|
| 201          | 10        |
| 202          | 10        |
| 203          | 11        |

Output

[
  {"flight_id":10,"booked_cnt":2,"waitlist_cnt":0},
  {"flight_id":11,"booked_cnt":1,"waitlist_cnt":0},
  {"flight_id":12,"booked_cnt":0,"waitlist_cnt":0}
]

Explanation

Flight 12 appears even without bookings. booked_cnt and waitlist_cnt are both 0 for flights with no passenger requests.

Example 3

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 50        | 1        |

Passengers:
| passenger_id | flight_id |
|--------------|-----------|
| 901          | 50        |
| 902          | 50        |
| 903          | 50        |
| 904          | 50        |

Output

[
  {"flight_id":50,"booked_cnt":1,"waitlist_cnt":3}
]

Explanation

Only one seat is available, so one booking is confirmed and the remaining three requests are waitlisted.

Constraints

0 <= number of rows in Flights <= 200000
0 <= number of rows in Passengers <= 500000
1 <= flight_id <= 1000000000
1 <= passenger_id <= 1000000000
1 <= capacity <= 1000000
flight_id values are unique in Flights
passenger_id values are unique in Passengers
Each Passengers.flight_id references an existing Flights.flight_id
Return one row per flight
For each flight: booked_cnt = min(capacity, passenger_count)
For each flight: waitlist_cnt = max(passenger_count - capacity, 0)
Return exactly columns: flight_id, booked_cnt, waitlist_cnt
Return rows ordered by flight_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: flight_id, booked_cnt, waitlist_cnt

Flights: | flight_id | capacity | |-----------|----------| | 1 | 2 | | 2 | 2 | | 3 | 1 | Passengers: | passenger_id | flight_id | |--------------|-----------| | 101 | 1 | | 102 | 1 | | 103 | 1 | | 104 | 2 | | 105 | 2 | | 106 | 3 | | 107 | 3 |

Flights: | flight_id | capacity | |-----------|----------| | 10 | 3 | | 11 | 1 | | 12 | 4 | Passengers: | passenger_id | flight_id | |--------------|-----------| | 201 | 10 | | 202 | 10 | | 203 | 11 |

Flights: | flight_id | capacity | |-----------|----------| | 50 | 1 | Passengers: | passenger_id | flight_id | |--------------|-----------| | 901 | 50 | | 902 | 50 | | 903 | 50 | | 904 | 50 |