I'm having difficulty understanding the answer to the last exercise in learning Subqueries. I don't really understand the logic behind how they formulated the sequence number. Don't get me wrong, I understand the syntax behind flight_sequence_number's assignment, but I don't understand the logic behind actually calculating the number.
Link to exercise: https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/subqueries-generalizations?action=lesson_resume
My questions are:
Why do we want to know the number of flights [who's outer ID is less than the inner's ID] and [who's outer and inner origins are the same]?
Why do we add 1 to this number that we've counted?
Why must the outer ID be less than the inner ID?
The description says that the sequence number is based on time. In what way does this computation relate to 'time'?
Code in question:
SELECT COUNT(*) FROM flights f WHERE f.id < flights.id AND f.carrier=flights.carrier) + 1 AS flight_sequence_number