All language subtitles for 3. Implementing Row number in SQL

So in the last lecture we discussed that we wanted to create a list of top three customers with maximum

orders from each state.

Now, what should we cover steps to perform this?

Let's see.

So first we will combine our customer tables and order tables so that we get the order detail of each

customer, how much each customer is ordering.

Then on this combined data, we will add row numbers using row number function.

And then once we have row number in front of each customer, we will filter only top three customers

with very close on this newly created grow number.

So these are the steps we want to perform.

Now let's look at our data again.

So this is a sample of our customer table.

This is a sample of our sales table.

Now, in sales table, we have multiple order IDs corresponding to customer IDs.

So we have multiple rows with the same customer ID.

We want to aggregate this data on customer level and get the total number of order IDs, total number

of sales, total quantity, etc..

Now let's go into PG.

So let's first select the top ten rows of our customer table.

Let's run this.

So we have all this data and our final table.

We don't need a segment, etc. So in our final table we will be just keeping customer ID, customer

name and state.

Now let's select the top ten rows from our sales table.

So here we have our deadline.

Order ID, order date, ship, date, etc..

And then we have sales quantity and discount and profit.

So to get the total number of orders for each customer, we want to count the distinct order IDs each

customer ID has.

So for example, for first two rows, order ID is same and the customer ID is also same.

So there is no point of contact.

This has to, since these two are the details of same order and we want to calculate the total number

of orders.

So we'll use distinct with order IDs.

Now let's combine these two tables.

So suppose our first table is customer table and we want to select all the columns for now.

Now, I will tell you what, all we want to select from all the table after we write the next part of

our query.

From.

Customer as a.

Then we want to use the left join since we want all the data of our customer table and only limited

data of our order table.

So left.

Join.

And then instead of using the order table, I will do some aggregation on customer level and order table.

So we'll write.

Select.

I am starting a sub query.

First, we want the customer ID since we want to match on customer ID, so.

Customer ID.

Then for each customer ID, I have on the count of distinct order IDs.

So I will count of thing.

Order early.

Now for this problem.

This data is sufficient for us, but we will use the same data for our other Windows function as well.

So I will also importing sales quantity and total profit from each customer into our combined table.

These two columns are enough for our row number problem, but I am importing this new sales quantity

and discount for our future problem statements.

So for sales, we can have total sales.

So we can write some of.

Sales.

Coma.

Similarly, some of quantities.

I will also rename this.

I was ordered numb.

Sales some of sales as.

Sales.

Total.

Some of quantity as.

One duty.

Total.

And then.

Sum of profit as profit total.

Now let's look again what information we are getting from our order table.

We are getting a similar ID count of distinct order IDs.

Total sales for each customer, total number of quantities for each customer, and total profit from

each customer.

From where we want this data, we want this data from our sales table.

And we want this data to be aggregated on customer ID, so we'll write.

But.

Somewhat.

I'd.

Let's be.

So this is our first table.

This is our second table from second table.

We want these many variables.

So we'll be.

Not.

Order them.

Cuomo.

B dot sales total.

Cuomo b not quantity total.

And then B dot.

Profit.

So we have two tables.

We are joining two table.

Let's just join this wheel right on a dot.

Because somebody is equal to B dot customer ID.

So now our query is ready.

This query is just for combining the two tables and bringing all the important information from all

the table.

So you can see that in front of.

Each customer.

Now I have total number of orders.

Total sales.

Total quantity and profit total.

Now let us verify this data once.

So let's select this customer ID and let's see if the total number of distinct order is five or not.

So limit.

This is the customer I.D. I want to inspect.

Right.

Select a sir from customer.

We're.

But some of the ID is equal to this.

The number of distinct authorities for this customer should be five.

So let's run this query.

We should select sales instead of customer since we want to get the orders from this customer.

Let's just run this.

You can see that I will also.

Order it by.

Or I'd.

Let's run this.

So you can see that first order, second or third order, fourth order and then fifth order.

One, two, three, four.

And then five.

So the total distinct orders on this customer are just five.

So you can see that our query is correct.

Let's run this again.

Now we want to use this data.

On this data.

We want to.

But order number on the basis of ordering them.

So what I will do is I will create another table and save this information in that table.

So I will write Create table.

Well, name it.

Customer.

Order.

Since it contains the detail of aggregate order for each customer, I am naming it as customer order.

As.

Now you can create tables like this by putting all the data that you are getting inside this record.

If I run this.

All the data that I was getting from this select statement is now stored in this customer order.

Let's look at customer order once more.

Select.

Start from.

Customer.

Marder.

Let's run this.

You can see that we are getting the same data.

Now we want to provide raw numbers to each customer.

On the basis of their number of orders in each state.

And we really don't want this additional column.

So I will just select.

Customer ID.

Cuomo.

Because some of the name.

Cuomo estate, comma.

Order them.

Order them is the variable which we created just a while ago.

Which contains the number of distinct authorities.

So order them and then we'll use the rule num function.

Rule number.

Or what?

Then we start the bracket partition by.

Now we want to partition by state since we want top three customer from each state.

So partition by state and then.

Order to buy.

We want to order our customers on the basis of number of orders.

And we created ordered them variable for exactly that purpose.

And we want the customers with maximum number of orders to be placed in first row.

So we want order by descending.

So write DSC will save this.

Variable as.

Hello.

And.

So this is the window function.

Row, number, row number is the keyword.

Then over is also a keyword partition.

By is also a keyword.

Then we have to mention the variable on which we want the group and then order by is also a keyword.

After that we want to mention the variable on which we want to order the data or provide row number.

And at last we providing us to this variable.

So we went on with this column from.

Our data.

Let's run this.

Should be ordering them.

Let's run this again.

This is our data.

Now, you can see that for the state of Alabama.

With customer ID DC 12850.

We have the total number of orders are nine.

So here in the row number we are getting one.

For the second customer.

The same is set.

The number of orders is it?

That's why we are getting to.

In the third.

The number of orders is again eight.

That's why we are getting the three.

So one, two, three.

And similarly, if the state changes the numbering again, it starts from one.

So for Arizona, the maximum number of orders is ten and we are providing this customer as row and one.

So you can check other rules as well.

And see that we have many customers from California and the maximum number of orders is 13.

From California.

So from each state, I'm getting the customer with maximum number of orders.

For example, in the District of Columbia, there is only one customer.

And that's why we are getting row and as one.

There are no entries of two and three since there is only one customer.

So you can see that.

We are getting this rule and variable for each estate and for each customer.

Now we want top three customers from each state.

And how can we find out that?

We can use the ro and variable to segregate such customers, for example, in New Mexico.

The top three customers are this.

In.

So Minnesota, the top three customers are this customer.

So we can clearly use a work loss on our roll number variable where I can only get 1 to 3 numbers.

So if I write.

The same query.

Right.

Where?

Drew mn is less than or equal to three.

I should get three customers from each state.

Let's run this.

So I'm getting an error because ROE and is not available.

Currently I'm getting an error because in the same statement we are creating and we are providing the

workflows.

So again, you can save this information into some other table and then use this condition.

Or you can write this whole definition of ROE and to workflows or another way is to.

Portrait inside a subquery select.

Start from now.

This is my table name.

And after we are creating this table name, I'm using a word clause.

So.

Let's run this.

Again.

Since we are using a subquery, we have to write as a And here, here.

Dot this.

Let's run this again.

Now look what is working fine.

You can see that from each state.

We are getting the three rows only with the maximum number of orders.

So from Alabama, we have these three customers from Colorado.

We have these three customers.

From Kentucky.

We have these three customers and so on.

So from each step we are getting just three customers.

So that's how we use rule number.

This is our first window function.

We'll be using the same dataset that we created that is customer order for our rest of window functions

as well.

Thank you.