Non-Correlated Subqueries III

The non-correlated subquery examples we've used so far utilized two tables (flights and airports), but we can also perform transformations on a single table. For instance, sometimes we need to aggregate in multiple steps - like taking an average of a count.

Imagine you’d like to know how many flights there are on average, for all Fridays in a given month from the flights table. First, we’d need to calculate the number of flights per day, and then we’d need to calculate the average based on the daily flight count for each day of the week. We can do this all in one step using a subquery:

SELECT a.dep_month,
       AVG(a.flight_count) AS average_flights
  FROM (
        SELECT dep_month,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3
       ) a
 ORDER BY 1,2;

The inner query provides the count of flights by day, and the outer query uses the inner query’s result set to compute the average by day of week of a given month.

Community Forums
Get help and ask questions in the Codecademy Forums
Report a Bug
If you see a bug or any other issue with this page, please report it here.