FAQs on the exercise Introduction to Window Functions
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!
SELECT
month,
change_in_followers,
SUM(change_in_followers) OVER (
ORDER BY month
) AS 'running_total'
FROM
social_media
WHERE
username = 'instagram';
Attaching the output as well:
My Question:
What piece of the code here tells the computer that you’re supposed to adding the previous row value + this row value and display that number in the 4th column?
I understand that SUM(change_in_followers) is the Window function being used here.
And ORDER BY within the statement is only used to put the table in correct order.
What I fail to understand is, how is the computer understanding the operation to be performed, the result of which is the first column?
I might be misunderstanding you but that’s exactly what a window function does, operates on the series of rows you provide it, change_in_followers in this case.
It is worth noting that SQL is inherently a declarative language, whilst you describe the basic logic it’s largely up to the engine to decide on the implementation.
Try using RANK() if it helps (the basic form just numbers the rows) you to visualise the rows and be aware the function used SUM in this case would be performed on each row in turn carrying the previous value (from 0 we sum row 1, this is result 1, this result is added to row 2 making result 2… and so on).
I understand that WINDOW function acts on the series of rows. Understood.
But what’s allowing it (or making it) add one row after the another? Are you saying that that’s an inherent implemention of WINDOW function (and the fact that there’s a SUM function here)
Is that a correct understanding of your answer? (Maybe not fully correct, but somewhat there?)
Yeah that’'s more or less it, if you hunt around you might find a more helpful description than mine but using window functions lets you operate on only some of the rows instead of all rows at once (like an aggregate function would). For example you could have a moving sum of 3 rows (the current row, one before and one behind). That’s your view or “window” on the data which you want to operate on, this “window” would then move OVER the rows you specified returning a new value for each row based on what the row can access (what you specify as OVER matters too).
The Window statement, including the function (first_value) - and everything that is specified as OVER - I imagine them to be a separate view or a window - upon which the window function is performed.
But as you’d see in the result table. (the very first output in the result screen) - everything has been ordered as well. The posts column that you see is not ordered in the original table (the second table) - but after putting in the Window statement, everything has been orderered by posts - which I something I have described in the WINDOW statement - and not outside of it (which we normally do …ORDER BY POST ASC;)
I don’t understand how everything got ORDERED when the ORDER BY function is within the bound of where I’m writing a WINDOW statement.
You partitioned it which is much like GROUP BY within a window function so it will be different. You can still add ORDER BY outside the window function afterwards if you want to set an order to all rows after the data is selected.
) below.
) to up-vote the contribution!
