Как соединить рейсы с одной допустимой пересадкой?

Условие

Таблица flights(id, origin, destination, departs_at, arrives_at) содержит конкретные рейсы с полными timestamp в одном часовом поясе. Найдите пары из A в C с одной пересадкой: от прибытия первого рейса до вылета второго должно пройти от 30 минут до 4 часов включительно. Диалект — PostgreSQL.

Ответ

Соедините пункт назначения первого рейса с пунктом отправления второго и ограничьте разницу времени. Одного совпадения города недостаточно: второй рейс может вылететь до прибытия первого.

SELECT f1.id AS first_flight, f2.id AS second_flight
FROM flights f1 JOIN flights f2
  ON f1.destination = f2.origin
 AND f2.departs_at BETWEEN f1.arrives_at + INTERVAL '30 minutes'
                       AND f1.arrives_at + INTERVAL '4 hours'
WHERE f1.origin = 'A' AND f2.destination = 'C'
  AND f1.id <> f2.id;

Полные даты важны для перелётов через полночь. Это учебное правило пересадки: реальные требования к аэропорту, терминалу, визе и багажу в условие не входят и не должны неявно считаться проверенными.