The “f.id < flights.id” part of the code did not exactly make sense to me. It was not exactly clear why we need to compare the same column from two identical databases.
After searching online and looking over the code several times, I realized that the goal of code is creating “flight sequence number” based on the id. With the same carrier, the flight with the smallest id would have “1” as the flight sequence number, the flight with the second smallest id would have “2”, etc. So “COUNT (*) … WHERE f.id < flights.id and f.carrier = flights.carrier” just counts how many flights with the same carrier have smaller id numbers.
This video provides a nice explanation using a different example, which might help understand the code better. https://www.youtube.com/watch?v=fwhSEqMnbgI