What alias-ing is going on here?

[kvsrkrishnan]

Can somebody explain the inner query and atleast 2 iterations please ?

[aidlo]

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…

[tagace34481]

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

[j_williams_j]

@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!

[jhomsey]

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.

[cloudslayer36331]

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.

[sarcasticchef]

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.

[cam.albuquer]

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.

[mangorita]

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;

Wow, a million thanks, I finally understood what happened here.

[jpritcha]

Like many others, this exercise gave me issues. Here’s what I learned after searching around for an hour or so.

1. AS is optional for declaring aliases,

2. 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.

[maehara]

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;

[py5575107693]

I have the same feeling. Feels like there’s a big gap between the first course and this one. Bit frustrating to be honest.

[script3520657026]

Thank you! You should be teaching this course lol.