SQLize | PHPize | SQLtest

with empty_flights as ( select f.departure_airport, f.actual_departure, a.airport_code, s.seat_no from flights f join airports a on f.arrival_airport = a.airport_code join seats s on f.aircraft_code = s.aircraft_code where not exists ( select 1 from boarding_passes bp where bp.flight_id = f.flight_id ) ), empty_flights_count as ( select departure_airport, date_trunc('day', actual_departure) as day_departure, airport_code, count(seat_no) as empty_seats from empty_flights group by departure_airport, day_departure, airport_code having count(*) > 1 ), cumulative_empty_seats as ( select efc.*, sum(efc.empty_seats) over (partition by efc.departure_airport order by efc.day_departure) as cumulative_seats from empty_flights_count efc ) select airport_code, day_departure, empty_seats, cumulative_seats from cumulative_empty_seats where day_departure notnull order by airport_code, day_departure