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 #get-help.

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

Need broader help or resources? Head to #get-help and #community:tips-and-resources. If you are wanting feedback or inspiration for a project, check out #project.

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 #community:Codecademy-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

1 Like

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

3 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?

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.

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