This is how I understood it.
The inner query works out the relative position of an id value by using COUNT(*) to get how many id values are below it.
As an example, just focusing on the inner query and manually adding the id as “815” and the carrier as “AA”
SELECT carrier, id
FROM flights
WHERE id < 815
AND carrier = "AA"
ORDER BY id;
You get this returned;
carrier id
AA 75
AA 194
AA 361
AA 391
id “815” is the (5th) next value, using “<=” would be better;
SELECT carrier, id
FROM flights
WHERE id <= 815
AND carrier = "AA"
ORDER BY id;
You get this returned;
carrier id
AA 75
AA 194
AA 361
AA 391
AA 851
If you then apply COUNT(*) you get its (relative) sequence number
SELECT carrier, id, COUNT(*)
FROM flights
WHERE id <= 815
AND carrier = "AA"
ORDER BY id;
carrier id COUNT(*)
AA 815 5
In the example they used “<” and it gives a value one less, hence the “+ 1” is added to fix the sequence otherwise it starts at zero.
SELECT carrier, id, COUNT(*) + 1
FROM flights
WHERE id < 815
AND carrier = "AA"
ORDER BY id;
carrier id COUNT(*) + 1
AA 391 5
The inner query is just to work out the sequence number, the outer query iterates through the whole table and feeding its current “row” id value (and carrier) into the inner query to find sequence value COUNT(*) for the id/carrier.