Hello. I am currently doing the SQL tutorial called: 5. Correlated Subquired I. The goal of this part of the tutorial is to find the id's of flights which whose flight distance is below the average flight distance for their carrier.
The entire code for the correct answer looks like this:
SELECT id FROM flights AS f WHERE distance > ( SELECT AVG(distance) FROM flights WHERE carrier = f.carrier);
I'm puzzled by the "WHERE carrier = f.carrier" clause. Does it let the program know, that we need to specificy the average for each carrier individually? If so, does this code below achieve the same result?
SELECT id FROM flights AS f WHERE distance > ( SELECT carrier, AVG(distance) FROM flights GROUP BY 1;