 Hello, everybody. In this lesson, we're going to be taking a look at window functions. Now, window functions are really powerful and are somewhat like a group by, except they don't roll everything up into one row when grouping. Window functions allow us to look at a partition or a group, but they each keep their own unique rows in the output. We're also going to look at things like row numbers, rank and dense rank at the end of this lesson. So before we jump into writing a window function and seeing how the syntax works, let's actually write out a group by, and then we'll compare the two when we actually do write the window function. Let's say we want to take this demographics table, and we want to take this gender and compare it to the actual salaries. So what we actually need to do, when you say join, and we're going to join on the employee salary, let's go like this, get rid of all of this, and we'll do salary. And we're going to say on and let's do dm and sal for the aliases. We'll say dm dot employee underscore ID is equal to sal dot employee underscore ID. Now we're going to come up here and we're going to say gender comma, and we want to look at the average salary. And we need to get rid of this right here. And we need to come down to the bottom and say group by gender. Now, let's go ahead and run this query, see if it works. And it did. So we have our gender, and we have our average salary from our salary table. And we can rename this as average, we'll do average underscore salary, just like that. So this is how group by works, it rolls everything up into one row. Now let's try doing something pretty similar, except we're going to use a window function. Let's come right down here. And let's paste this. And let's start writing out our window function. Now we don't have to use the group by we're going to go ahead and get rid of that. And right here for gender, we can keep that the exact same. All we're really going to change is this part right here, we're going to say average salary. And that is part of creating a window function, typically with a straightforward window function, all we have to put is over with a closed parentheses. This is going to say we're looking at the average salary over and normally in here you'll specify something and we'll get to that in a little bit. But we're just going to look at an average salary over everything. So let's go ahead and run this output. So this is going to look a little bit different, right? So the male and female all have their own individual rows, which is not the same as group by and this average salary is looking at the average salary of everybody. We're not breaking it out by the gender like we did up here. Here we rolled it up. Now we're looking at the average salary for the entire column. Now what we can do is actually partition by now partition by is going to separate it out kind of like grouping it. So let's say partition, partition by and we'll say gender. So just like when we did the group by the group by rolled everything up into one row, this is not going to roll everything up, but it is going to perform this calculation based off of the different genders, the unique values in this column. Let's go ahead and run this. And if you'll notice the female is 53,750 the male 57,428. Now let's go compare these. I'm going to run this and this query. Let's run this. So if we look at our group by, it's the exact same numbers, except we have it on their own individual rows. Now why would we want this? Well, let's say we want additional information. So let's just look at this one for now. So in this one, let's say we wanted to add additional things like the first name. So we'll do dm dot first underscore name, we can do last or dm dot last underscore name. So we can add other information. And it doesn't affect this column at all, because we're using a window function. If we try to add these exact things, I'm going to go up here and do it. If we try to add these exact things to this, let's see if yeah, that works. And then we also have to group by this. If we run this query now, it's going to be completely different because we're using a group by we're grouping by the first name, the last name and the gender, we're breaking everything out based off of the unique values in these columns. Whereas down here, it's completely independent of what's going on in these other columns. All we're doing is we're doing a window function, just based off of that column. So I think that's pretty amazing. And there's a lot of additional functionality that we can do with these window functions. And we're going to take a look at a lot of those things in just a little bit. Let's try another example really quickly. Let's literally just copy this, paste it down here. And all we're going to do is we're going to change this to sum. So now instead of the average salary, we're looking at the sum of salaries. And we're still partitioning by the gender. Go ahead and run this. And let's pull this up. So all the men together make $402,000. All the females make $215,000. Now what we're about to do is something called a rolling total. If you've never heard of a rolling total, a rolling total is super cool and can be done within my sequel. A rolling total is going to start at a specific value and add on values from subsequent rows, based off of your partition. So all we have to do is add an order by, and we're going to order by let's say the employee underscore ID. Let's go ahead and take a look at this. And it looks like the employee ID is ambiguous. I had a feeling. So I just need to say DEM dot employee ID. Let's try this one. So now we have something called a rolling total and I'm going to actually name it as rolling underscore total. Because this is super cool that window functions can do this. And this is something that a lot of people in like finance do. I did it myself when I worked in healthcare. And it partitions based off the female, and you can't see the employee ID, but there's an employee ID that we're kind of ordering on in the background. Now what it's doing is it's starting with Leslie Nope, and she made 75,000. Then the next person, April, she made 25,000 equals 100. And just to actually see this better, I'm going to add salary. And so Leslie Nope had 75,000. Then we're adding this 25,000 to the 75 and we get 100. Then we're adding the 60,000 to 160,000. Then we're adding 55,000 to 215,000. So we're adding every single time we're adding this salary to the already existing total, all the way up to our grand total, which was 215,000. The exact same thing happens with the males. So we start with 50,000, then we add 50, then we had 90, then we had 70, all the way up to 400 and 2000. Now you can do this in a lot of different configurations on a lot of different columns, but in essence, this is exactly what a rolling total is. That's how it works. And we were able to partition based off of this column. We don't have to use partition by we could do this completely regardless of the partition, but I thought it was interesting to at least break it out by female versus male. So now that we know how to use a window function, let's look at some special things that you can really only do with window functions or window like functions. So we're going to bring this down. And what we're going to do is get rid of this entire thing. And we're going to look at something called row number. Then we're going to look at rank and then we'll look at dense rank. So it's like a row underscore number. And this is just like an aggregate function, like we're doing the average age or average salary or something like that. This is what we're doing. We're doing a row number. Now we're going to do this over. And we'll just do everything for right now. So let's go ahead and run this and just see what it looks like. Let's bring it up. And what we're doing is we're saying, okay, we have first name, last name, gender, salary, that's all great. Then we get to row number. And we're doing a row number based off of everything. It doesn't matter what it is. So we're starting at one, which is the very first row. And we go all the way down to the bottom, just like an employee ID. So let's actually add that. Let's do dm.employee underscore ID, just like this. So we have this one, two, three, four, five, six, seven, eight, nine, 10, 11. Now, if you remember on this table, we are missing Ron Swanson. So it kind of skips that, but it's basically like an employee ID, we're kind of giving it its own unique value. And these row numbers are not going to repeat itself. If you do it like this, now they can repeat themselves. If we do a partition, and let's do a partition on the gender again, because we know how to do that one, we'll do partition by, let me spell that right, partition by the gender. Now we're going to add a row number based off the gender. But again, it's broken out or partitioned by gender. Let's look at this. Now it goes for the females one, two, three, four, then for the males, it restarts one, two, three, four, five, six, seven. Now this is just in a random order based off how the data was stored in the table itself. Now what if we wanted to kind of rank these based off of the highest salary first down to the lowest salary? You nailed it, we just add an order by. We'll order by salary. If you want to do it from highest to lowest, giving the highest salary, the number one, and the lowest salary later down, we'll do descending. And let's run this. And you'll see that for female, we're still partitioning by gender. For female, the highest salary is one. Next is two, three and then four. Then for males, the highest salary is one all the way down to seven. So that's what row number does. It just gives a row number based off of whatever you're partitioning by or ordering by in your window function. Now let's go over here and add a comma. And we're going to add, and let's go down just a hair. Let's add rank. So we want to do rank, and we'll do our parentheses. Now rank is going to give it more of an official rank. And let's see how this works. So we'll do rank, and we'll do over partition by salary descended, the exact same thing. And while we're here, I'm going to rename these, I'm going to say as, as row underscore num. And we'll call this one rank underscore num. So let's go ahead and run this. And it looks very, very, very similar, except for one small thing, this right here. So when we're using the row number, whatever we are partitioning by, it's not going to have duplicate rows within that partition. It just won't. So even if there's 50,000 right here, it's just going to automatically assign it based off of something that is running in the background, whether it's the order of how the data is stored in the table, or some other order by that you are using on the table. Now rank is a little bit different, because rank is going to take it just like it did the row num, except when an encounters a duplicate based off of the order by which is the salary, it's going to assign it the same number. So this is five and five. What's unique about rank is that the next number is not going to be the next number numerically, it's going to be the next number positionally. So this is one, two, three, four, five. And this is kind of like a six. And then it goes to seven. So it skips number six. Now there's another one. Let's copy this rank. There's another type of rank called dense rank. And we'll do dense underscore rank. So we'll do dense underscore rank. And let's run this. And let's pull this up. There we go. Now dense rank is ever so slightly different than rank in the fact that when it gets down to duplicates, it's still going to duplicate them. So it's going to have a five and a five. But it's going to give the next number numerically, not positionally, that is the only real difference between rank and dense rank. And again, row number is just not going to have duplicates, it's going to give it its own unique within that partition. So I know I just threw a lot at you, but that's row number rank and dense rank in a nutshell. And you can review this mess around with it, all of these things because, you know, these are actually really, really useful. So that's all we're going to take a look at in this window functions lesson. I hope all of that made sense. I hope you kind of got an understanding of how it can work and how powerful these window functions can be. And this is actually the last lesson in the intermediate MySQL series. Thank you guys so much for watching. I really appreciate it. If you liked this video, be sure to like and subscribe, and I'll see you in the next video.