- 4 days ago
Window functions in PostgreSQL become much easier to understand in this lesson, where row-by-row calculations are broken down with clear examples. The walkthrough starts with the basics of row number, then shows how partition by can reset numbering within each species while order by sorts results by estimated net worth.
From there, the lesson compares rank and dense rank using tied values, making the difference between gaps and no gaps easy to see. It also moves into rolling sums and rolling averages, including how to limit the window with rows between two preceding and current row for more practical analysis.
If you want a PostgreSQL tutorial that explains window functions, ranking functions, moving averages, and rolling aggregates in a simple, beginner-friendly way, this lesson is a strong place to start. It is especially useful for SQL practice, interview prep, and learning how to analyze data without collapsing rows with group by.
From there, the lesson compares rank and dense rank using tied values, making the difference between gaps and no gaps easy to see. It also moves into rolling sums and rolling averages, including how to limit the window with rows between two preceding and current row for more practical analysis.
If you want a PostgreSQL tutorial that explains window functions, ranking functions, moving averages, and rolling aggregates in a simple, beginner-friendly way, this lesson is a strong place to start. It is especially useful for SQL practice, interview prep, and learning how to analyze data without collapsing rows with group by.
Category
📚
LearningTranscript
00:00What's going on, everybody? Welcome back to another video.
00:02Today, we're continuing our PostgreSQL series.
00:04In this lesson, we're taking a look at window functions.
00:07Now, window functions have been notoriously confusing for a lot of people,
00:11but I'm going to try to break it down really simply
00:13so you understand the main types of window functions and how to use them.
00:17Before we dive in, it's actually come right down here,
00:20and I'm going to pull up this image
00:21because I think this describes window functions pretty well.
00:24When I think of a window function,
00:26I actually compare it a lot to something like a group by,
00:28where you're taking multiple rows of data
00:31and you're aggregating them down to one row.
00:34This is fantastic.
00:34We love group by and aggregate functions within SQL,
00:38but window functions, right over here, you'll notice we take it row by row,
00:43but then we apply it to each row.
00:46We don't aggregate it all into one row.
00:48So this is window functions in its simplest terms,
00:51but there are window functions that do different things,
00:54and we'll get into all of those in this lesson.
00:56Now let's get rid of this because we're going to start writing out some window functions,
01:00and I'm just going to give us some room really quick,
01:02and we're going to keep that everything just in case we want to see it.
01:06Let's take everything, and let's take our character underscore name.
01:11Then let's come down here.
01:12Let's add some tabs, and we're going to start with row number.
01:15I think row number is kind of the simplest window function,
01:18and it's the easiest to explain, and then we'll kind of go on from there.
01:22So we're going to do row underscore number, and then we're going to say over,
01:27and this is everything we're going to write.
01:29Now this row number function is going to apply a single number to each row.
01:34That's all it's going to do.
01:36Now this over right here is a specific keyword.
01:39It's a function that tells us how to look across the rows without grouping them,
01:43so it won't be like an aggregate function.
01:45It'll handle it row by row.
01:46Now let's run this because this is just the simplest one that we're going to see.
01:50Let's go ahead and run this, and there we go.
01:54So we have Luke Skywalker, which is 1, Leor Organa 2, 3, 4, all the way down to 12.
01:59We really didn't give it any information here.
02:02We just said apply a row number.
02:03That's all we did.
02:05Now let's come back up here.
02:07What we can do is we can make this a little bit more advanced.
02:11Maybe we want to give it a row number, but we want to break it out by the species.
02:14So we want to say within the species, give it a number.
02:17So we're going to use something called partition by.
02:22Now partition by is kind of like a grouping.
02:25We're grouping it by the species, and then we're applying this row number.
02:30But we're not actually grouping it, right?
02:32We're keeping it row by row, but we're going to apply that row number for each of the species.
02:38So let's break it out by species, and let's bring the species up here as well.
02:45And let's go ahead and run this.
02:47And I think I forgot a comma here.
02:49Let's add our comma.
02:50And we actually may need to add an alias to this.
02:52I know sometimes it requires an alias.
02:56Let's go ahead and try to run this now.
02:57And there we go.
02:59So now we're breaking it out by species.
03:01It already kind of has it ordered for us, right?
03:03We have droid, 1, 2.
03:05And then it resets at the next species.
03:07So Gungan 1, Human, 1, 2, 3, 4, 5, 6, Unknown 1, Wookiee 1, Zabrak 1.
03:13And so what it's doing is we're partitioning it by each species, and then we're applying the row number to
03:21that partition.
03:22Now right now, we are just giving it a random row number based on the species.
03:27That's it.
03:27We're just numbering within each species, but that isn't super helpful.
03:31Let's go back to our data really quick.
03:34And let's say we want to do it based off of the estimated net worth.
03:39So let's add this in here.
03:40We'll say estimated net worth, comma.
03:45And then we're going to come right down here.
03:46Now, since we're adding one more thing in here, I'm going to kind of make it a little easier to
03:52read.
03:54And put it like this.
03:55So I'm going to say partition by, and then I'm going to say order by.
03:58Now we're going to order by this estimated net worth.
04:02So let's take this right here, and I'm going to say descending.
04:05So I want the people with the highest estimated net worth to get the first row number.
04:10Before we run this, here's what we're doing.
04:12We're assigning a row number over, and the over is taking it row by row, but we're giving it some
04:17extra context.
04:18We're partitioning it by the species, so we're applying row number within the species, and then we're ordering it by
04:24the estimated net worth.
04:25So let's go ahead and run this entire thing.
04:29So now we have a droid, and we have another droid.
04:32Right here, we see their estimated net worth.
04:35R2-D2 has a higher estimated net worth than C-3PO, so R2-D2 is number one, and C-3PO
04:41is number two.
04:42Now let's go down to the humans.
04:44We have all of our humans right here.
04:46We have our estimated net worth going highest to lowest,
04:49and so we have our numbers going one all the way down to six.
04:54And so within the human species, we are giving them a rank based off of their estimated net worth.
04:59Now in this exact example, we're kind of ranking them,
05:02and if we scroll back up, we actually have two functions called rank and dense rank,
05:06and both of these functions are used for these exact circumstances,
05:11and let's take a look at the difference between these window functions.
05:14So let's copy this entire thing just so we preserve it, and let's come down here,
05:18and we're going to change this one to rank.
05:21Now rank and dense rank are super, super similar, except for one tiny difference,
05:27and we're going to see that difference between Han Solo and Obi-Wan Kenobi.
05:31So take a look at these people right here and how they actually rank them.
05:37So let's just keep it as rank.
05:39Everything is the same.
05:41Let's go ahead and run this.
05:43Right down here, for Han Solo and Obi-Wan, they have the exact same estimated net worth.
05:48They're tied in net worth.
05:50And so what it does is it's going to say 1, 2, 3, 4, 4,
05:54but then it doesn't have a fifth place after it.
05:58It goes right down to sixth place.
06:00So if there were 10 other people who had 30,000,
06:04it would go 4, 4, 4, 4, 4 for 10 people,
06:06and then it would start at 15 or 16.
06:09But let's look at dense rank.
06:11And this is the only difference between rank and dense rank right here.
06:15So let's run this.
06:17So now you'll see we have Han Solo goes 30,000, 1, 2, 3, 4, 4.
06:22But now Luke Skywalker, it picks up at the next numerical number.
06:27And so that is the only difference between rank and dense rank.
06:30And this is a question you might get in like an interview or something.
06:33And so knowing the difference between these can be very useful,
06:35not just in the real world, but also if you get asked this in an interview.
06:39So we've already covered a ton of things.
06:41And we can, you know, change these up by partitioning by different things,
06:45by ordering them on different things.
06:47But I think what we're going to do is we're going to start over
06:50and we're going to look at things like moving averages,
06:53moving sums, and things like that,
06:55because these are especially helpful as well.
06:57I use these quite a bit,
06:59especially when I'm working with money or finances or things like that.
07:03So what we're going to do is we're going to come right here.
07:05We're going to say character underscore name.
07:07And then we're going to bring in our estimated net worth.
07:12Let's add our comma.
07:13Let's come down here.
07:14And now we're going to use just a typical aggregation.
07:17We're just going to say sum.
07:18And let's just keep it like this really quickly.
07:21And we're going to say sum over.
07:22Now let's just run this.
07:25Now what we're going to be doing is this over right here
07:28takes us row by row.
07:30And in fact, let me just get our default data in here.
07:33And so let's go down here.
07:35And so we have this sum over.
07:38Now all this over is going to do is take it row by row by row.
07:42We aren't partitioning on anything just yet, right?
07:46So this is the simplest form.
07:48But what we're going to do is we're going to take our estimated net worth
07:50and we're going to sum every single one of these estimated net worths,
07:54but then apply it to each row.
07:57So let's go ahead and run this.
07:59And so our sum right here is 24,104,000.
08:05Yeah, 24 million.
08:06So 24 million is being applied to this because we're summing every single row,
08:10but we're applying it to each row at the end in its own column.
08:14And we can name this.
08:15We can say as total net worth.
08:19And we'll keep it really simple.
08:20So all we're doing is we're summing across everything
08:23and then we're applying it.
08:24That's all we're doing.
08:25But what if we add one simple thing?
08:28And that's going to be an order by.
08:30So I'm going to come right over here and I'm going to say order by.
08:34And then we'll do estimated net worth.
08:36And let's start from the smallest.
08:38We'll make it ascending.
08:39And we'll make that to the largest.
08:41So I'm just formatting it so you can kind of easily visually see what we're doing here.
08:46But we're summing the estimated net worth over,
08:49but now we're ordering it by the estimated net worth lowest to highest.
08:54Let's go ahead and run this.
08:56So now we're not simply summing everything at one time and applying it.
09:00Now we're doing it row by row ordered by the estimated net worth.
09:04So here we have 500.
09:05We start with 500.
09:07Then we add the 1500.
09:08We get 2000.
09:10Then we add the 2000.
09:11We get 4000.
09:12The 50,000.
09:13We get 54,000.
09:14The 120,000.
09:15The 174,000.
09:16So you can see we're just going zigzag pattern back and forth.
09:21And so this is really helpful.
09:22This is what's called a rolling sum.
09:25As we go down, we're just adding the next row into this total net worth until we get to the
09:30bottom.
09:31And that's where we add up every single one.
09:33And that's our final number.
09:35And just like we did before, we could also add a partition in here.
09:39So let's just add in our species.
09:41And I need to spell species, right?
09:43And we'll add in a partition just like we did before.
09:46And I'm just going to bring it over here.
09:47We're going to say partition by.
09:51Then we put our species.
09:52So now we're going to take it species by species.
09:55And then we're going to do our rolling sum.
09:58So let's go ahead and run this.
10:00So now we're doing droids.
10:02So we have 1500.
10:03Then we're adding the 2000 to get 3500.
10:06Let's go down to human.
10:07We start with our 150,000.
10:09We add in our 300,000.
10:11And then we add in.
10:12And because these are actually tied, these get combined into one aggregation.
10:17So we actually add 15,000 to 60,000, which equals 75,000.
10:21And that's just one weird quirk.
10:23Now, there are ways to actually get around this.
10:26But it gets a little complex.
10:27You have to have like a CTE.
10:29And then you assign a row number.
10:31And then you can add it row by row.
10:32But that gets a little bit advanced.
10:34And we haven't even covered CTEs yet in this series.
10:36And then we add our 5 million and then our 8 million and then our 10 million.
10:41And so this is our rolling sum partitioned by the species.
10:45Now, let's change this really quick.
10:46Just changing the aggregation.
10:48We're going to say this is our average estimated net worth.
10:52So instead of a total net worth, we're going to say our average underscore net worth.
10:59Now, let's run this and just see how it acts.
11:01So now what we're doing is we're taking 1,500 and then we take the 2,000 and we're averaging
11:07between them.
11:08So now our average between these two numbers is 1,750.
11:12Now, we're going to do the same thing.
11:14But in this one, we're averaging all of the previous ones that we do.
11:19So when we get to the bottom, we're just taking an average of all of these.
11:22So for 150,000, it's only 150,000.
11:25But when we add these two together, it's 250,000 because we're taking into account all of these.
11:30Then when we add in the 5 million, it's 1.4.
11:34Then we add in the 8 million, it's 2.75.
11:36Then we add in the 10 million, it's 3.958.
11:40So this is what's called a rolling average.
11:42Now, this in its simplest form is totally fine.
11:44I'm going to give you a real use case.
11:46A real rolling average won't look back six months, a year, 10 years, whenever.
11:51You're going to do it chunk by chunk.
11:53Maybe you look back one month and you look forward one month and that's your average for
11:57your current month.
11:58Or maybe you just look back two months and you can actually specify that in something
12:03like this.
12:03So let's look at how this syntax would look because what we want to do, especially for
12:08this human, because there's lots of humans, we only want to look back a certain amount.
12:12So let's run this.
12:13We're going to say rows between.
12:17And this should look very familiar.
12:19If you watch the string and date functions lesson, this should look very similar for date
12:23functions, but we're going to say rows between two preceding and current rows.
12:31Now let's go ahead and run this and then we'll take a look at it.
12:33If we go down to human, so this is going to be the exact same.
12:36What we're doing is taking the current row and we're getting the average, but what we're
12:41going to be doing in the next ones is we're only taking the two previous ones and the current
12:46row.
12:46So let's take a look at this one.
12:48We're taking this one plus these ones.
12:51But then when we get to the 5 million, we're only taking these.
12:55We are not taking this one into account because this is three preceding.
12:59Then when we get to the 8 million, we're now only taking into account these ones because
13:04this is the current row plus the two preceding.
13:07And then we get to the 10 million.
13:08It's only taking into account these ones because the current and the two preceding.
13:13This is probably a more accurate rolling average than taking it all the way back.
13:17So we can come up here and this one would now probably more accurately be called a rolling
13:24average.
13:25Now, even before we added in the two preceding and current row, I guess you could call it
13:29a rolling average, although you're taking every single previous value.
13:33But if you just look at maybe one or two months behind and you average those out, those are probably
13:38a more accurate average to what you're currently working with depending on your data and the scenario
13:43in which you're using.
13:44And so those are some of the major window functions.
13:46We're going to take a look at three more window functions in the next lesson that are kind of
13:50different, right?
13:51It's called lag, lead, and end tile.
13:53They're a little bit different than the window functions that you're probably going to use for the
13:57most part, which are the ones that we covered in this lesson.
14:00And so I'm going to have one more lesson before we then look at CTEs, which are common table
14:04expressions, which are so useful.
14:06I really hope you learned something in this lesson.
14:08If you did, be sure to like and subscribe and I will see you in the next lesson.
14:13Bye.
14:14Bye.
14:21Bye.
14:21Bye.
14:22Bye.
14:23Bye.