Non-Correlated Subqueries III - Why the inner query has by_date?


#1

I'm confused. Why the inner query has by_date since it only asks average flight by day_of_week and month??

The correct code is this:

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
LIMIT 10;

Why not this:

SELECT a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM (
SELECT dep_month,
dep_day_of_week,
SUM(distance) AS flight_distance
FROM flights
GROUP BY 1,2
) a
GROUP BY 1,2
ORDER BY 1,2
LIMIT 10;


#2

This is de correct code:

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;


#3

I have the same question, what is the point of dep_date, why do we need it in the code. I know what the correct code is already but it doesn't make sense @webblaster18704


#4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.