Correlated SubQueries II


#1

Correlated SubQueries II

https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-ii?action=resume_content_item

Hello,

I’m having trouble comprehending what each line in the code is doing. It’s easiest to use the example instead of the actual question. Below is the code and I added the WHERE and ORDER BY line at the bottom of it to see what the result is doing easily. I understand the result, I don’t understand how the pieces are taking us there. I won’t be able to re-create this in a real life scenario if I ever needed to. Here is the prompt and code for it:

“It would also be interesting to order flights by giving them a sequence number based on time, by carrier.
For instance, assuming flight_id increments with each additional flight, we could use the following query to view flights by carrier, flight id, and sequence number:”

SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights

WHERE carrier = ‘AS’
ORDER BY 1,2 desc;

The results show that flight_id 1581 was the first time carrier “AS” flew and on flight_id 19963 was the 18th time carrier “AS” flew.
The +1 is so that it counts from 1 to 18 instead of 0 to 17.

The part I don’t understand is WHERE f.id < flights.id
I experimented a little:
< and > simply reverse which way it counts. For example with > flight_id 19963 becomes 1 and 1581 becomes 18.
= turns every flight_sequence_number into 2.
<= and => does the same as < and > but as if you had + 2, resulting in 2 to 19 for flight_sequence_numbers

Could someone please explain what is happening with WHERE f.id < flights.id? I can’t figure it out.

Thank you.