select
    airports.city AS city,
    avg(subq.cnt) as average_flights
from
    (select
         arrival_airport,
         EXTRACT(day from flights.arrival_time) AS day_number,
         count(flight_id) as cnt
     from
        flights
     where
         CAST(arrival_time AS date) BETWEEN '2018-08-01' AND '2018-08-31'


     group by
         arrival_airport,
         day_number
    ) as subq
inner join airports on airports.airport_code = subq.arrival_airport
group by
    airports.city;