I'm trying to understand what this code does and I'm having an issue tying it's implimentation to the questions it's supposed to be answering. Here's what I understand starting with the inner query.
First the select statement returns four columns, three fields and one total sum of flight_count from the flights table. Then, it is grouped... 1,2,3? Not quiet sure, it's organized in some way I guess. Then the a at the end is a nickname for the 4 columns that will be supplied to the main query.
The main query selects two columns from the inner query with the... something AVG AS thing... Then it groups by. Yeah, it confuses me a little too much.
AVG(a.flight_count) AS average_flights
COUNT(*) AS flight_count
GROUP BY 1,2,3
GROUP BY 1,2
ORDER BY 1,2;