SELECT
    sub.city AS city,
    AVG(sub.flights_amount) AS average_flights
FROM
    (SELECT
         airports.city AS city,
         EXTRACT(day FROM flights.arrival_time) AS day_number,
         COUNT(flights.arrival_time) as flights_amount

     FROM
     airports
     INNER JOIN flights ON flights.arrival_airport = airports.airport_code

     WHERE
         CAST(arrival_time AS date) BETWEEN '2018-08-01' AND '2018-08-31'
     GROUP BY
         airports.city,
         EXTRACT(day FROM arrival_time)
    ) AS sub

GROUP BY
    sub.city;