CREATE TABLE airports (
    port_code VARCHAR(10) PRIMARY KEY,
    city_name VARCHAR(100)
);

CREATE TABLE flights (
    flight_id varchar (10),
    start_port VARCHAR(10),
    end_port VARCHAR(10),
    start_time datetime,
    end_time datetime
);

-- delete from airports;
INSERT INTO airports (port_code, city_name) VALUES
('JFK', 'New York'),
('LGA', 'New York'),
('EWR', 'New York'),
('LAX', 'Los Angeles'),
('ORD', 'Chicago'),
('SFO', 'San Francisco'),
('HND', 'Tokyo'),
('NRT', 'Tokyo'),
('KIX', 'Osaka');

-- delete from flights;
INSERT INTO flights VALUES
(1, 'JFK', 'HND', '2025-06-15 06:00', '2025-06-15 18:00'),
(2, 'JFK', 'LAX', '2025-06-15 07:00', '2025-06-15 10:00'),
(3, 'LAX', 'NRT', '2025-06-15 10:00', '2025-06-15 22:00'),
(4, 'JFK', 'LAX', '2025-06-15 08:00', '2025-06-15 11:00'),
(5, 'LAX', 'KIX', '2025-06-15 11:30', '2025-06-15 22:00'),
(6, 'LGA', 'ORD', '2025-06-15 09:00', '2025-06-15 12:00'),
(7, 'ORD', 'HND', '2025-06-15 11:30', '2025-06-15 23:30'),
(8, 'EWR', 'SFO', '2025-06-15 09:00', '2025-06-15 12:00'),
(9, 'LAX', 'HND', '2025-06-15 13:00', '2025-06-15 23:00'),
(10, 'KIX', 'NRT', '2025-06-15 08:00', '2025-06-15 10:00');

-- select * from airports;
with f_dtl as (
select f.*,a1.city_name as start_city, a2.city_name as end_city
    from flights f
left join airports a1 on f.start_port=a1.port_code
left join airports a2 on f.end_port=a2.port_code
)
select fd.start_city,fd2.start_city as middle_city,coalesce(fd2.end_city,fd.end_city) as end_city
    , case when fd2.flight_id is null then fd.flight_id 
    else concat(fd.flight_id,',',ifnull(fd2.flight_id,'')) end as flight_id
    ,TIMESTAMPDIFF(MINUTE,fd.start_time,coalesce(fd2.end_time,fd.end_time)) as time_taken
from f_dtl fd
left join f_dtl fd2 on fd.end_city = fd2.start_city and fd.end_time<=fd2.start_time
where fd.start_city='New York' and (fd.end_city='Tokyo' or fd2.end_city='Tokyo')