OK, one of my favorite topics. So this is where we break away from what I call intermediate SQL, which is like joins and unions and getting super familiar with working with nulls and a lot of the topics that we covered as part of that Creating Datasets and Visualizing Insights course. And we really move into some of what I would call the advanced SQL or the beginnings of the advanced SQL concepts, which is using things like ranking and numbering functions. So if you've ever heard of the rank function or people using row number, or dense rank or leading and lagging to actually iterate between rows in the output, this is where we can start to get really dangerous with SQL. So let's take us through a few examples. OK, so this is where we mentioned the concept of a window function. So what ultimately what we want to do here is we start with, all the way in the left, a table of employees at an imaginary company. And they're part of two different departments. Let's say department one is sales. And department two is analytics. And each of the different employees have a tenure at the organization. So this is when they first started. And the question that we want to answer at the end of the day is rank everybody by each department in how long that they've actually been there. Now think about that. We actually have to break apart our original data set, kind of like a group by, right, into two different departments. But then we also have to rank those records. So yeah, what you could do technically is you could do a select from the employee list there and then where department equals one and then do your ranking there via an order by on the start date and then mash that together with a union again for that other department for the department number two there. But that's not as scalable. Say instead of two departments, there were 10 departments. You don't want to keep breaking things apart manually with where clauses and then unioning them back together. And that's why we're going to introduce the analytical window function. And one of them in particular is called RANK. So the SQL we're going to review on the next slide, but let's review conceptually what we're doing. OK, so on the left, we have the list of employees and their tenure. The first thing we have to do is we have to split it apart by department. So that's where we're invoking this thing called partitions. We're partitioning by department. And this is taking one data table. And now we're breaking it apart so we can operate on separate chunks of that. So that's that second column there. And then next, we need to give it a logical ordering. So what's the whole point of actually us breaking this apart? Well, we ultimately have to answer the question of who's been there the longest. In order to do that, we need to get our records in a sensible order before we apply a rank of the rows, so 1, 2, 3, 4, 5, that sort of thing. So that third column of information there has the sorted results there. So you can see Jacob there for department number one has been there the longest since 1990. And Isabella in department two has been in that department since 1997. And last but not least is just actually applying a rank function, which is going to be a new column all the way at the end. And you can see that we're just simply applying the ranks of the rows 1, 2, 3, 4, 5. And there is-- in the case of ties, you could actually use-- for rank, it's going to be 1-1. And then you could say 2, 3, 4. Or if you just wanted the dense rank, one of the functions, I believe it's dense rank, will actually-- ties are still counted as 1-1. But it'll actually skip over and say 1-1 and then 3. So I'm pretty sure that's the rank whereas the dense rank will actually go 1-1 and then 2. Anyways, so recap of what we actually just did starting from left to right. We have an employees table. We're splitting it apart into different subgroups or windows. And then we're ordering on it. And then last but not least, we're applying that ranking function, just one of the available analytical functions. These are called analytical functions that we're using on this data. So what is the actual SQL query look like? Here we go. So we've got the first name of the employee, the department. And again, this is actually how we're going to break apart that singular table into those different chunks, right? Those different chunks we're going to call partitions. And then we've got the start date, which is how we're actually going to get value out of why we're breaking them apart in the first place because we have to answer the question of who has the most tenure at the firm. And that last field is the calculated field there where it's the analytical function that we're invoking. So it's saying perform a rank operation over in parentheses. Here's how we're going to set up those windows of data. Here's how we're going to partition it. So we're going to say partition by the department, meaning break apart that singular table into those two different departments or however many different departments. And while you're at it, store each of those by a sorted order, where it's start date. And again, order by defaults to ascending in this case, and for dates, that means oldest to newest.
