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 () 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 () below!
You can also find further discussion and get answers to your questions over in Language Help.
Agree with a comment or answer? Like () to up-vote the contribution!
Why doesn’t Last_Value select the largest number (of posts) for the PARTITIONED BY username when the ORDER BY call in the window function sorts the values in ascending order?
In other words: I’m looking for some clarity on why “RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” needs to be used in the window function when using Last_Value.
Thanks!
Hope someone can explain this as well - I really do not understand how that “RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” code functions.
i came here with the exact same doubt. I am trying to figure out why it works for FIRST VALUE and not for LAST VALUE. The exercise 3 of this section tell us that “This is because each row in our results set is the last row at the time it is outputted.” The hint of the exercise 2 tells us that “Here we can see it is pulling the last value for each row. This is not showing us the last value for any user but instead the current row (which would be the last value!)”
In an abstract manner I can sense that the way our Data is set within the parentheses leads the command to select the FIRST or LAST VALUE of the column posts from the table Social Media. Since the set is partitioned by username, the query seeks the Last Value of each row that represents a username. So in order to scan the posts colum we need to tell the query to seek all values of the post column, hence the RANGE command from a value that has no precedent to a value that has no successor.
I am just writing it here but that doesn’t mean I quite understood it. Just trying to brainstorm here.
I didn’t really understand it at first, so I learned a little about this.
The RANGE (or ROWS) clause is always used with ORDER BY clause and specifies a range in each partition after sorting rows. Unless we specify a range, the default range seems to be from the first row of each partition to the current row. To set a range between row A and row B, write
RANGE (row A) BETWEEN (row B)
"… PRECEDING" means a row before the current row and "… FOLLOWING" means a row after the current row. Especially UNBOUNDED PRECEDING means the first row of each partition and UNBOUNDED FOLLOWING means the last row of each partition.
In Step 1 and 2, we set implicitly:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
So LAST VALUE is the value of the current row.
In Step 3, we changed the end row of the range to the last row of each partition:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Then LAST VALUE is the value of the last row of each partition.
Actually I just googled with words such as SQL, UNBOUND PRECEDING, etc, browsed some sites, and followed the one that was easy for me to understand. It’s written in Japanese so I’m not sure if it’s useful for you, but here’s the link:
why can’t we just change the ORDER BY to be DESC ?
SELECT
month,
username,
posts,
FIRST_VALUE (posts) OVER (
PARTITION BY username
ORDER BY posts desc
) most_posts
FROM
social_media
ORDER BY username, month;
looks like that proves we get the result we want and is a lot easier?
Well to that end, why not just use MIN and MAX? The answer is that we’re learning about a specific aspect of window functions in an ostensibly accessible way. To your point, that will work and it will be less complicated in THIS situation, but I imagine there are more complicated scenarios where this is more useful (say, when analyzing strings that don’t fall neatly into any order).
I tried this and it gets the same answer as the provided hint in exercise. Why can’t we use this by default i.e. is there a specific use case where this might not work?
Also do not understand why LAST_VALUE will not work if the ORDER_BY technically already puts it in an ascending order, and you’re querying for the last row value in posts.
) below.
) to up-vote the contribution!