Created
January 9, 2022 20:01
-
-
Save bnm3k/2bc48e8514eb62a68dbb5dd611ffcb15 to your computer and use it in GitHub Desktop.
SQL Recursive queries: solution for exercise 3 (https://habr.com/en/company/postgrespro/blog/490228/)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment