FAQ: Window Functions - LAG

This community-built FAQ covers the “LAG” exercise from the lesson “Window Functions”.

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

Analyze Data with SQL

FAQs on the exercise LAG

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!
You can also find further discussion and get answers to your questions over in Language Help.

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

Need broader help or resources? Head to Language Help and Tips and Resources. If you are wanting feedback or inspiration for a project, check out Projects.

Looking for motivation to keep learning? Join our wider discussions in Community

Learn more about how to use this guide.

Found a bug? Report it online, or post in Bug Reporting

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!

there’s no file for the exercise.

Yall this exercise got me trippin for real

5 Likes

The line

PARTITION BY artist

doesn’t seem to make a difference. Will still get the same output regardless of whether or not it is included because of the line

 WHERE artist = 'Lady Gaga'

Kind of makes it confusing why we were required to do the last step with PARTITION BY

Also, the instruction does not mention that we also need to include chart_position in our selection, so without looking at the hint there would have been no way to know that I had to select chart_position to proceed. Also, it is requiring that the selection of chart_position be in the specific position between the two window functions which was never mentioned in the instructions.

There is also the requirement that the subtraction be flipped so that the sign changes, because that is “a good thing”. Not really sure how thinking to do it this way would necessarily come automatically. Maybe change the instruction to say: “show change in chart position, where getting closer to topping the chart is considered a positive change”.

9 Likes

In the context of this exercise, what exactly is LAG()'s third argument?

It says it’s optional and that it designates what to replace default NULL values with, which makes sense looking at the first part pf the exercise, where the third argument is 0 (default NULL values are simply made 0 then).

In the last part of the exercise, LAG()'s third argument is suddenly the column name - same as argument 1. No explanation given and the documentation I am reading is not illuminating.

What is argument 3 for?

1 Like

The lesson says that the third argument of LAG is " what to replace default null values with".

If we look at the third argument initially, 0, and compare that to what it was changed to, the column name streams_millions, it makes more sense to use streams_millions.
To make this clear, let’s examine the behavior when 0 is used and when streams_millions is used:

  • Third argument is streams_millions:
    What this does is assume that, if a null is encountered, performance was the same as last week, meaning there was no change from the previous week, and we will get an output of 0 for streams_millions_change

  • Third argument is 0:
    What this does is assume that, if a null is encountered, there were no streams/views at all, which would not make sense if the streaming service was still active, since of course people would be watching.

The third argument is used for specifying the assumption that you would make, if data was missing.

6 Likes

You explained this very clearly and concisely, thank you for that! I just wish your response could be added to this lesson. Generally speaking, I feel that this lesson is a bit wishy-washy and could do with a bit more conciseness.

1 Like

Agreed, this was confusing! It is much clearer when you remove the WHERE clause and run the report for all artists. You then can see that without PARTITION we get non-sense answers as our LAG will now returns a value not from the prior row of the set of rows for that artist, but from all the preceding rows and therefore could be of any artist who happened to have a row with same week value as as the row we are evaluating (as that is all we are ORDERING BY).

1 Like

I’m doing this exercise two years after you, but I had the same reaction. I can’t understand why I need to partition by user if I’m defining the username that I want results for. PARTITION BY is used when you are including all members of a column (usually the first column). I tested it without the PARTITION BY command and it works just the same. Also, it gave me the green checkmark when I removed the PARTITION BY.

Thanks for your post. Nice to see that others are dealing with the same things.

I just wish that Codecademy would actually iron out the problem with exisiting content like this, that has been languishing for two years! Every time they ask for feedback, I say “don’t sacrifice fixing existing content for pushing out new ones”

They should go through all the feedback for existing material and make updates accordingly.

1 Like

Capture 28