Leetcode HARD 2793 - Status of Flight Ticket WINDOW RANK() CASE WHEN-Solved by Everyday Data Science

Опубликовано: 21 Октябрь 2024
на канале: Everyday Data Science

370

Question: https://leetcode.com/problems/status-...

SQL Schema:
Create table if not exists Flights(flight_id int,capacity int)
Create table if not exists Passengers (passenger_id int,flight_id int,booking_time datetime)
Truncate table Flights
insert into Flights (flight_id, capacity) values ('1', '2')
insert into Flights (flight_id, capacity) values ('2', '2')
insert into Flights (flight_id, capacity) values ('3', '1')
Truncate table Passengers
insert into Passengers (passenger_id, flight_id, booking_time) values ('101', '1', '2023-07-10 16:30:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('102', '1', '2023-07-10 17:45:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('103', '1', '2023-07-10 12:00:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('104', '2', '2023-07-05 13:23:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('105', '2', '2023-07-05 09:00:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('106', '3', '2023-07-08 11:10:00')
insert into Passengers (passenger_id, flight_id, booking_time) values ('107', '3', '2023-07-08 09:10:00')

Pandas Schema:
data = [[1, 2], [2, 2], [3, 1]]
flights = pd.DataFrame(data, columns=['flight_id', 'capacity']).astype({'flight_id':'Int64', 'capacity':'Int64'})
data = [[101, 1, '2023-07-10 16:30:00'], [102, 1, '2023-07-10 17:45:00'], [103, 1, '2023-07-10 12:00:00'], [104, 2, '2023-07-05 13:23:00'], [105, 2, '2023-07-05 09:00:00'], [106, 3, '2023-07-08 11:10:00'], [107, 3, '2023-07-08 09:10:00']]
passengers = pd.DataFrame(data, columns=['passenger_id', 'flight_id', 'booking_time']).astype({'passenger_id':'Int64', 'flight_id':'Int64', 'booking_time':'datetime64[ns]'})

#datasciencequestions #leetcodesolutions #dataengineering