SELECT
    T.city,
    AVG(T.flights_amount) AS average_flights
FROM
(SELECT
    city,
    EXTRACT (day from flights.arrival_time) AS day_number,
    COUNT(flights.flight_id) AS flights_amount
FROM
    flights
INNER JOIN
    airports
ON
    airports.airport_code = flights.arrival_airport
WHERE
    CAST(flights.departure_time  AS date) BETWEEN '2018-08-01' AND '2018-08-31'
GROUP BY
    city,
    day_number
) AS T
GROUP BY
    city