Тестовый запрос: использование агрегатной функции max

WITH validities AS (

SELECT validity, lower( validity ) AS validity_begin, upper( validity ) AS validity_end

FROM ( SELECT DISTINCT validity FROM bookings.routes ) AS validities

), dates AS (

SELECT validity, tz, dt::date, extract( isodow FROM dt ) AS dow, validity_begin, validity_end

FROM validities v

CROSS JOIN ( SELECT DISTINCT timezone FROM bookings.airports ) tz(tz)

CROSS JOIN generate_series(

timezone( tz, v.validity_begin )::date::timestamp,

timezone( tz, v.validity_end )::date::timestamp,

'1 day'::interval ) dt

), schedules AS (

SELECT r.route_no, a.timezone AS tz, r.validity, r.scheduled_time, unnest( r.days_of_week ) AS dow

FROM bookings.routes r

JOIN bookings.airports a ON a.airport_code = r.departure_airport

), dates_to_be AS (

SELECT s.route_no, s.tz, s.validity, s.dow, timezone( s.tz, d.dt + s.scheduled_time ) AS scheduled_dep

FROM schedules s

JOIN dates d ON d.validity = s.validity AND d.tz = s.tz AND d.dow = s.dow

WHERE timezone( s.tz, d.dt + s.scheduled_time ) >= d.validity_begin

AND timezone( s.tz, d.dt + s.scheduled_time ) < d.validity_end

), absent AS (

SELECT dtb.route_no AS route_no, dtb.tz, dtb.validity, dtb.scheduled_dep AS sched_dep_to_be

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN bookings.airports a ON a.airport_code = r.departure_airport

RIGHT JOIN dates_to_be dtb ON dtb.route_no = f.route_no AND dtb.validity = r.validity AND dtb.scheduled_dep = f.scheduled_departure

WHERE f.route_no IS NULL

AND dtb.scheduled_dep < (SELECT max(scheduled_departure) FROM flights)

), excess AS (

SELECT f.route_no, a.timezone, r.validity, f.scheduled_departure AS sched_dep_not_to_be

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN bookings.airports a ON a.airport_code = r.departure_airport

LEFT JOIN dates_to_be dtb ON dtb.route_no = f.route_no AND dtb.validity = r.validity AND dtb.scheduled_dep = f.scheduled_departure

WHERE dtb.route_no IS NULL

)

SELECT

(SELECT count(*) FROM absent) absent_flights,

(SELECT count(*) FROM excess) excess_flights,

CASE

WHEN (SELECT count(*) FROM absent) > 0 AND (SELECT count(*) FROM excess) > 0 THEN 'ERROR: absent and excess flights'

WHEN (SELECT count(*) FROM absent) > 0 THEN 'ERROR: absent flights'

WHEN (SELECT count(*) FROM excess) > 0 THEN 'ERROR: excess flights'

ELSE 'Ok'

END verdict;