Original subtitles

In the last few lectures, we have discussed rank functions and and title function.

Now let's move on to discuss some aggregate windows function.

So the first function we are going to discuss is average function.

We have already seen average in our aggregate functions when we were using group by.

We can use the average function with windows function as well.

So, for example, we are using the same data.

We have customer store and revenue.

Now suppose in front of each one of these rows, I want the average revenue of that store.

For example, there are three customers from a story that is C one, C five and C seven with revenue

100, 203 hundred.

So the average for a store A is 100 plus 200 plus 300 divided by three.

That comes out to be 200.

So the average of store A is 200.

So I want the average value of 200 to be in front of each one of these customers, seven, C, five

and C, seven.

Similarly, I have on the average value of store B in front of each customer who are purchasing from

store B.

So I want a final table.

Something like this.

So for store A, the revenues are 100, 203 hundred.

So the average revenue should be 200, 202 hundred.

Similarly, for store B customers, the values are 300, 300, 204 hundred.

If you add all this and divide it by four, the average comes out to be 300.

So I want 300 in front of each customer from a store.

B, this is the final table we want.

We can get this table from using average Windows function.

And how can we do that?

The syntax is average.

Then you have to mention the field for which you want the average.

Then again, just like any other window function, we have to use partition by and order by here.

Order by is not required because there is no use of order.

So in partition by you have to select the groups on which you want the average to be calculated.

So again, only two columns are required.

First column is on which you want to calculate the average value and second column which we are going

to use in partition, by which is the group in which you want to calculate the average value.

So now let's calculate the average of revenue of each estate in our database.

Let's look at our table.

Customer underscore order.

That we created in our previous lectures.

So we have customer ID.

Customer name.

State and.

We also have sales total.

So now let's calculate the average sales of each state.

Now, how can we do that?

Feel right, Select.

Let's set customer ID.

Customer name.

Then let's head straight.

That said the total revenue or total sales.

So.

Sales.

Total.

We will name it as revenue.

And then let's calculate the average in each state.

So we'll write average.

We want the average of this sales total.

So we will just write average of total.

Well.

Right?

What?

And the bracket will write partition by.

We want to partition it on the column of state.

So state let's save it as.

Average revenue.

So we are getting.

Five columns, customer ID, customer name, ID, state revenue of that particular customer, and the

average revenue of the whole state in which that customer belongs.

From.

Customer underscore order.

Let's run this.

You can see that we are getting this result.

We have summarised customer name state.

Revenue of each customer, and then we have the average revenue of the entire state.

So for the state of Alabama, the average is this.

Similarly.

For the state of Arizona.

The revenue is this.

So one use case of calculating average can be.

So suppose if you want to create a list of customers whose revenue is less than the average revenue

of their estate.

So suppose if some stores want to target customers who are buying less from their store.

So how can they calculate the customers who are purchasing less than the other customers?

So we are commending it and.

If we want to select, we can just.

Put this in the bracket.

We can select.

Start from this table.

There.

Revenue.

There's less than.

Average.

Revenue.

Right.

Write a dot.

Average revenue is less than.

Aid revenue is less than the average revenue.

Since we are using a as alias for this entire table.

Now if I run this.

You can see that we are only getting the customers who are buying less than the other customers.

So this is the use case of average window function.

In the next lectures we will discuss count and some functions as well.

Thank you.