00:00:00

Description

Editorial

Flight Seat Occupancy and Wait Queue Summary

EASY10 pts

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:
1. flight_id
2. booked_cnt
3. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Flights =

[{"flight_id":1,"capacity":2},{"flight_id":2,"capacity":2},{"flight_id":3,"capacity":1}]

Passengers =

[{"passenger_id":101,"flight_id":1},{"passenger_id":102,"flight_id":1},{"passenger_id":103,"flight_id":1},{"passenger_id":104,"flight_id":2},{"passenger_id":105,"flight_id":2},{"passenger_id":106,"flight_id":3},{"passenger_id":107,"flight_id":3}]

Loading problem...

101

00:00:00

Description

Editorial

Flight Seat Occupancy and Wait Queue Summary

EASY10 pts

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:
1. flight_id
2. booked_cnt
3. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Flights =

[{"flight_id":1,"capacity":2},{"flight_id":2,"capacity":2},{"flight_id":3,"capacity":1}]

Passengers =

Flight Seat Occupancy and Wait Queue Summary

Hints

Flight Seat Occupancy and Wait Queue Summary

Hints