FAQ: Subqueries - Correlated Subqueries II


#1

This community-built FAQ covers the “Correlated Subqueries II” exercise from the lesson “Subqueries”.

Paths and Courses
This exercise can be found in the following Codecademy content:

SQL: Table Transformation

FAQs on the exercise Correlated Subqueries II

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!


#2

I just don’t understand what this question was ? " Using the same pattern, write a query to view flights by origin, flight id, and sequence number. Alias the sequence number column as `flight_sequence_number .". I had to get the solution to see what the code was doing. I seriously did not follow this chapter so far. There should be better examples.

Thank you
Sivarama Krishnan (kvsrkrishnan)


#3

I had the same problem, and have been having similar problems with the next few lessons as well.


#4

SELECT origin, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.origin=flights.origin) + 1
AS flight_sequence_number
FROM flights;

Here we query based on origin and id rather than carrier and id as shown in the example.


#5

But what does that achieve? What are you trying to query here? The Correlated subqueries are used for row-by-row processing, so for every orgin and id in the outer query… what are you executing in the inner query?


#6

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


#7

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…


#8

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


#9

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


#10

This is a very bad example of a Correlated subquery. It’s too complicated (because of lack of explanation and level of difficulty )to get the point across. Copy/pasting and tweaking the code to arrive at the solution is not practical as in the real world there’s no such luxury.
I think the SQL lesson falls apart in this chapter; everything else is very well explained and makes sense.


#11

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.


#12

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.


#13

How does the query know that the ID for each carrier should be sorted. When I run
SELECT carrier, id
from flights
where carrier=‘MQ’
LIMIT 4;
I get
|MQ|17107|
|MQ|7869|
|MQ|2205|
|MQ|14979|

How does the query know to tag the lowest ID in the entire set as 1?
For example, carrier MQ, the first ID is 1878 and there are a total of 32 IDs. So 1878 is less then 31 IDs, why is it tagged as “1”.
|carrier|id|flight_sequence_number|
|MQ|1878|1|
|MQ|2205|2|
|MQ|3068|3|

I think I understand but I am getting the inverse. If I count the number of times the Lowest ID is less than the rest of the IDs and Add 1, so I get the highest number, and the inverse for the highest number which is Count of 0 since it is never lower than any ID, so plus 1 gives me 1. The query returns the opposite where the highest ID has the highest count and the lowest ID is a count of 1… Why?


#14

I just figured it out now. I reversed the tables I was comparing. It looks at the outer table ‘flights’ and for each ID, it sees if it is BIGGER than the inner table ‘f’. So for the smallest ID in ‘flight’ it is NEVER BIGGER than any ID in ‘f’ so the count is 0. We then ADD 1 to it and get it the sequence number of 1. And so on and so forth… and that is how the world works


#15

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.


#16

Can someone tell me why we’re adding 1? I’m losing my mind trying to figure this out.


#17

Let’s try to think this through, and hopefully be able to relate back to the narrative. I’m going in blind right now, since I don’t have that in front of me, only a post from above.

Consider,

(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier)

is a number. The adding of one must be creating an incremented value, suggestive of extending a sequence.

We need to refer back to the narrative for some hint as to why the above is implemented, but at least we’ve given it some thought, to this point. Now to find the real explanation!