SELECT
    airports.city,
    AVG(SUBQ.total) AS average_flights
FROM
    (SELECT
        COUNT(arrival_airport) AS total
     FROM
         flights
     INNER JOIN airports ON airports.airport_name = flights.arrival_airport
     WHERE
     CAST(arrival_time AS date) >= '2018-08-01' AND CAST(arrival_time AS date) <= '2018-08-31'
    ) AS SUBQ
GROUP BY
    airports.city;


missing FROM-clause entry for table "airports"