I'm still squinting sideways at 6/7 but here's my interpretation (not sure if it's the same as what @yeshus is saying, though I think it may be):
SELECT origin, id,
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
'f' is a variable, and could be any row from the 'flights' table. This allows us to compare every row of 'flights' to every other row, in order to place them into a sequence.
The inner query is counting how many instances there could be where the (variable) value of an id in 'f' is less than the value of the current id being assessed in the 'flights' table, for that same carrier.
For example, where flight.id is 75 and carrier is AA, it will find 0 instances of a possible lower value of f.id among other AA flights as 75 is the smallest number in the id list.
It then adds 1 to that number. We do this because the first item in a list is usually numbered 1, not 0. Thus, the flight with id of 75 will be assigned the sequence number '1'.
Similarly, where flight.id is 391 and carrier is AA, it will find 3 instances where the variable f.id could be lower (flight ids 75, 194 and 361). 3 + 1 = 4, so flight.id 391 will be assigned flight_sequence_number 4 among the AA flights. And so on.
Note that if you delete the line:
then it will assign flight_sequence_numbers from 1 right through to 499 and not restart the numbering for each carrier.
I hope my reasoning here is sound and that it helps someone else out there struggling with the somewhat scant explanations for this module! (No doubt they'll improve those over the coming months.)