Original subtitles

Now, the first window function we are going to discuss is rule number.

As the name suggests, raw numbers gives the raw number in a separate column.

You can also provide this raw number on the basis of groups and on the basis of your given order on

which you want to sort.

No, we will just take this example to understand this function.

Now suppose I have three columns in my data set.

The first column is customer, where I have the different customer IDs for each customer.

The second column is state.

Here I have a state corresponding to each customer.

So as you can see, there are two kinds of state.

And then the third column is orders.

Here we have the total number of orders that each customer has placed in a given amount of time.

Now, suppose in each state I want to give rule number to the customers according to their number of

orders.

For example, for State A I have one to give rule number one to the customer with maximum number of

orders.

So I want value as one in front of my customer.

See five Since he or she has placed maximum number of orders in a state a.

Seven has placed three orders and SI five has placed six orders.

So I want to get one in front of see five two in front of C one and then three in front of C seven.

Similarly, in State B, I have four customers with order five, four, two and four.

Since C two has placed maximum number of orders in state B, I want to give c2a value of one, then

C three and C six has placed four orders and then C four has placed two orders.

So I want my ordering in this order inside the group of states.

So this is the final table I have want.

We can use rule number to get this column.

Now here.

This is the syntax of row number, which is the keyword is row number.

Then we will write over and then partition by and order by and partition by.

We have to mention the column on which we want to partition our data or group our data.

For example, here we want our customer to have row numbers within a group of states.

So.

So I will write partition by store.

And on what basis.

We want to provide these raw numbers.

We want to provide this raw numbers on the basis of their number of orders.

So in order by I will write orders.

And since we want this in descending order, we'll read the SC.

So let's look at the output we get after running this code.

So this is the output we will get.

C five with maximum number of orders in state, one will get rule number as one.

Then the second highest number of orders in state A is CE one with three orders.

So C one should be second.

Similarly.

C7 seven has the lowest number of orders, so.

The third row should be off C seven.

Similarly.

Now let's move to a state B.

Here we are using partition by state.

The row numbering will start with a state B, so the maximum number of orders in the state B is of C

two.

So we will get C two as one, then C three and C six have same number of orders, but since C three

is appearing first.

So C three should get second row number.

Then comes C six with four orders, and at last we'll get C four with just two orders and row number

of four.

So if you look at the definition, it will also suggest the same thing unique number for each row within

the partition, with different values for tied values.

So here we had a tie, but we get unique values in the row number.

So that's our first window function.

Now let's use it in the data that we are using.

So suppose we want to create a list of top three customers with maximum number of orders from each state?

So we have customer table, we have sales table.

Now we want to create a list of top three customers with maximum orders from each state like this.

In the next video, we'll see how to do that.

Thank you.