Questions regarding SUBQUERIES III

[ajaxslayer35269]

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>

I have several questions about this task:

1. why do we need an “a”?
2. why AS is not used here?
3. why do we put “a.” in the first SELECT?
4. why do we need GROUP BY in inner query? it defines the way you see the output, isn`t it?

<Below this line, add a link to the EXACT exercise that you are stuck at.>
https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii?action=lesson_resume

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

```

SELECT a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM (
SELECT dep_month,
dep_day_of_week,
dep_date,
sum(distance) AS flight_distance
FROM flights
GROUP BY 1,2,3
) a
GROUP BY 1,2
ORDER BY 1,2;

<do not remove the three backticks above>

[fguardini]

This guy has given a very detailed reply (even too detailed maybe): https://discuss.codecademy.com/t/non-correlated-subqueries-iii-a-few-questions/40537

Then again the code itself looks more complicated than what it could actually be. I get the same results by using:

select dep_month, dep_day_of_week, avg(distance) as average_distance
from flights
group by 1,2
order by 1,2;

I think they chose a question that is not really suited for an inner vs outer query type of excercise…

[pyjumper77657]

select r.dep_month,
r.dep_day_of_week,
AVG(r.flight_count) AS average_flights

   from (SELECT dep_month,
          dep_day_of_week,
           dep_date,
           COUNT(*) AS flight_count
      FROM flights          
    where dep_day_of_week ='Friday'
     GROUP BY 1,2,3) r  
     GROUP by 1       

             
     ;

we can choose any name , here i choose r as a temporary table , in the given solution they choose a as a name of this table , but we ask as the AVG of flight per month for every Friday , so we need this condition ( where dep_day_of_week =‘Friday’)
i hope that i give you some explanation

[rawbubba9891]

im having the same problem