SQL Recursive queries: solution for exercise 3 (https://habr.com/en/company/postgrespro/blog/490228/)

ex3.sql

with recursive p(hops, flights, last_stop, last_arrival, total_duration, closest, found) as (
  select *
  from (
    select 
      array[departure_airport, arrival_airport] as hops,
      array[flight_no] as flights,
      arrival_airport as last_stop,
      scheduled_arrival as last_arrival,
      scheduled_arrival - scheduled_departure as total_duration,
      rank() over(
        partition by flight_no, departure_airport, arrival_airport 
        order by scheduled_departure asc
      ) as closest,
      arrival_airport = 'CNN' as found
    from flights 
    where 
      departure_airport = 'UKX'
      and scheduled_departure >= (bookings.now() - '20 days'::interval)
  ) as t where closest = 1
union all
  select * from(
    select 
      (hops || f.arrival_airport)::char(3)[] as hops,
      (flights || f.flight_no)::char(6)[] as flights,
      f.arrival_airport as last_stop,
      f.scheduled_arrival as last_arrival,
      p.total_duration 
        + (f.scheduled_arrival - f.scheduled_departure) -- flight duration
        + (f.scheduled_departure - p.last_arrival), -- waiting period before departure
      rank() over(
        partition by flight_no, departure_airport, arrival_airport 
        order by scheduled_departure asc
      ) as closest,
      bool_or(f.arrival_airport = 'CNN') over()
    from flights f
    inner join p on f.departure_airport = p.last_stop 
    where 
      f.scheduled_departure >= p.last_arrival
      and not f.arrival_airport = any(p.hops)
      and not p.found
  ) as t 
  where closest = 1
) 
select hops, flights, total_duration
from (
  select
    hops,
    flights,
    total_duration,
    rank() over(order by total_duration asc)
  from p
  where 
    p.last_stop = 'CNN'
) as t 
where rank = 1