FAQ: Subqueries - Correlated Subqueries II


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


If we assume that we have just five flights and we want to count them we can do this in two ways:

1<1 - FALSE count is 0
1<2 - TRUE count is now 1
1<3 - TRUE count is now 2
1<4 - TRUE count is now 3
1<5 - TRUE count is now 4

To have an accurate number of flights we have to + 1,

or to just write a logical expression with <=:

1<=1 - TRUE count is now 1, in that situation, we don’t have to add 1 at the end.

So in this example,
we can just write logical expression without plus 1:

WHERE f.id <= flights.id
AND f.carrier = flights.carrier)

Hope it helps! :slight_smile: