Can somebody explain the inner query and atleast 2 iterations please ?
I do agree with you. no explanation why the code is as it is⌠(unless iâve skipped a bit)
why the âAS fâ
why the âa.â
i can copy and paste and replace well, but it doesnât mean i understand what is happeningâŚ
Guess iâll have to read around other forums to find out whyâŚ
Its a bit disappointing, not as well thought through (or perhaps properly tested) as the initial SQL courseâŚ
yeah I feel like up until now everything has been explained really well, but the AS f and the a stuff has flummoxed me, I canât see where the concepts here have been explained, so I donât really understand whats going on
@kvsrkrishnan @tagace34481 I agree about the lack of explanation and no hints makes for a frustrating lesson.
As far as I understand the as f and as a examples allow you to create an alias for the query so you can reference it easier.
In this is example:
select origin, id,
(select count(*)
from flights f #calling the inner query of flights f allows us to distinguish between the inner and outer query
where f.id < flights.id
and f.origin=flights.origin) + 1
as flight_sequence_number
from flights;
As far as the answer and what this query achieves - I found it easier to understand the example:
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;
which produces (first 5 lines):
|carrier|id|flight_sequence_number|
|---|---|---|
|MQ|17107|25|
|DL|19471|57|
|AA|194|2|
|HP|19905|13|
|WN|11155|41|
flight_sequence_number is telling us how many times that carrier has flown. So, MQ is on its 25th flight when its ID is 17107 for reference the id for its first flight is 1878 and id for the second flight is 2205. The query is telling us to keep counting the flights up until the last recorded input id which is 19867 for MQ.
Try the order by query to get a better idea of what it is counting:
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights order by 1, 2 desc;
Hope that helps!
I agree. I even went back and re did the whole previous SQL course over again, expecting that maybe I missed something but still am having a very hard time getting through this.
Thanks for this. I understand now of what the inner query is accomplishing.
I still donât understand why the previous exercise uses AS f
for the outer query in the previous exercise as opposed to just the f
for the inner query in this exercise.
Also, I still donât understand how this assigns the sequence number by time like the beginning of the exercise states. It appears to assume that the id
is somehow related to time. However, if I add the dep_date
column to the result set and then ORDER BY origin, flight_sequence_number
, itâs pretty clear that the id
isnât really sorted by time at all.
I was starting to lose my mind going through this whole lesson until I came to the forum. Glad to know Iâm not the only one who is frustrated by the lack of good explanations.
Does anyone have an answer yet for why the alias flights table in this example was written as âflights fâ instead of âflights AS fâ. Disappointed the lesson didnât mention anything on that.
Thank for explaining, this was very helpful!
I am also still strugglng since the beginning of this âTable Transformation with SQLâ course, I think it is a huge jump from the previous one and everything is not explained well. Your addition of the âORDER BYâ line made much more sense then the whole exercise attempt.
Wow, a million thanks, I finally understood what happened here.
Like many others, this exercise gave me issues. Hereâs what I learned after searching around for an hour or so.
-
The table aliasing creates some ambiguity. The intent of the query becomes clearer if we refactor the original query a little bit.
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;
Explicitly aliasing both the inner and outer references to the flights table we get the following:
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights AS inner_f
WHERE inner_f.id < outer_f.id
AND inner_f.carrier = outer_f.carrier) + 1
AS flight_sequence_number
FROM flights AS outer_f;
To me, this version makes whatâs going on much clearer. It seems somewhat analogous to a nested for loop in any general purpose language, something akin to:
for outer_f in rows(flights):
count = 0
for inner_f in rows(flights):
if (inner_f.id < outer_f.id
and inner_f.carrier == outer_f.carrier):
count += 1
print(outer_f.carrier, outer_f.id, count + 1)
For each subquery execution, all rows of inner_f are iterated through in the WHERE clause, while outer_f is held constant at a single row.
This explanation helps me understand what is going on. Same table columns have been compared to calculate sequence number by each carrier. Thanks!
To show what is the objective of this query more easier with following query.
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights AS inner_f
WHERE inner_f.id < outer_f.id
AND inner_f.carrier = outer_f.carrier) + 1
AS flight_sequence_number
FROM flights AS outer_f
ORDER BY 1,3;
I have the same feeling. Feels like thereâs a big gap between the first course and this one. Bit frustrating to be honest.
Thank you! You should be teaching this course lol.