In this article, we will see what are the **SQL Window Functions** and common issues that we face with **practical examples**.

**SQL Window Functions**

We have 3 different categories of window functions, which are given below.

**Aggregate Functions**– These are AVG, SUM, MIN, MAX, COUNT e.t.c**Ranking Functions**– These are RANK, DENSE RANK, ROW_NUMBER e.t.c**Analytic Functions**– These are LEAD, LAG, FIRST_VALUE, LAST_VALUE e.tc

With all these analytical functions we can use the over by clause.

For our better understanding we will write some queries and see the behaviour of these functions.

Interview point of view these are very important.

Let us consider the below table.

Now let us write a query to get the average on bonus column

SELECTAVG(Bonus)FromEmployee;

Result for this query will be

AVG(Bonus) |
---|

550 |

Now we will write another query to capture all other records from the employee table along with average.

SELECTEmployee ID, Name, Work, Bonus,AVG(Bonus)FROMEmployee;

Results for the above query will be

What happened here?. If you can obeserve, I got a different average salary for each employee. The last employee I got the correct value which is 550.

Let us see the below diagram to understand how this average is being computed.

Here we have not specified the explicit value for the rows or arrange clause. So that is the reason all average values for the bonus column is being computed differently except the last value.

Since we have not specified explicit value it is going to take the default value.

It is going to take the **Unbounded Preceding **and** Current Row**.

Let us understand what this value means.

Say for example this average function is being applied to the second row. In that context what is this range between unbounded preceding and current row?

**Current row** is the one which average being computed and **unbounded preceding** is the window for this average function starts at the first row within the result set.

Now the order by clause is emposed on the rows for the employee table that is our result set.

Within the orderset results **unbounded preceding** means the window starts for this **average function** at the first row.

When it is in the second row the **range of average** is for the first row and **current row**.

Similarly when we are on the third row the range is **first row** and the **current row** which is **third row**. So average of these three salary would be captured for the third row.

At the moment this is how it is being calculated. Now look at the last row it is calculated correctly. Why because it is going to calculate the average for all records.

We will have the same problem whenever we use the any of window functions that we have **aggregate functions**, **Ranking Function** and **Analytical Functions** because of the default value for that clause.

Now in the query we will also take **Count** and **SUM**

For that our query will be

SELECTName, Work, Bonus,AVG(Bonus)OVER( ORDER BY BONUS)AS[Average]COUNT(Bonus)OVER(ORDER BY BONUS)AS[Count]SUM(Bonus)OVER(ORDER BY BONUS)AS[SUM]FROMEmployee

Results for this query will be

The window for these functions is, It has to starts with the first row and ends with the last row.

But what is the default value here? Which is the range between **unbounded preceding** and **current** **row**.

Now i am going to change that range between **Unbounded Preceding **And** Unbounded Following**. Means window ends with the last row within the result set.

Now let us writte a query for this

SELECTName, Work, Bonus,AVG(Bonus)OVER( ORDER BY BONUSROWS BETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING )AS[Average],COUNT(Bonus)OVER(ORDER BY BONUSROWS BETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING)AS[Count],SUM(Bonus)OVER(ORDER BY BONUSROWS BETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING)AS[SUM]FROMEmployee

Results for this query will be

What we achieved here. Total count, Total average and Total sum for all rows. This is what we expected here.

At the moment there is no partition. If the partition is involved this is going to slightly change.

Now let us take the partition by work. Here window starts with partition first row and ends with the partition last row.

Let us write a query to achieve this

SELECTName, Work, Bonus,AVG(Bonus)OVER( PARTITION BY Work ORDER BY BONUSROWSBETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING )AS[Average],COUNT(Bonus)OVER( PARTITION BY Work ORDER BY BONUSROWSBETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING)AS[Count],SUM(Bonus)OVER(PARTITION BY Work ORDER BY BONUSROWSBETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING)AS[SUM]FROMEmployee

Results for this query will be

Now there is one more thing here I want to calculate the **one row preceding **and **one** **row following** how i can do that?

Let us see.

If you can take an average. Consider I am in third row and I want to calculate average between **second row** and **fourth row**.

Let us look at the first row we dont have any row before that and one row after that it calculate average, sum and count respectively .

Let us write a query for this

SELECTName, Work, Bonus,AVG(Bonus) OVER( PARTITION BY Work ORDER BY BONUSROWSBETWEEN1 PRECEDINGAND1 FOLLOWING )AS[Average],COUNT(Bonus)OVER( PARTITION BY Work ORDER BY BONUSROWSBETWEEN1 PRECEDINGAND1 FOLLOWING )AS[Count],SUM(Bonus)OVER(PARTITION BY Work ORDER BY BONUSROWSBETWEEN1 PRECEDINGAND1 FOLLOWING)AS[SUM]FROMEmployee

Results for this query will be

Here you need to calculate the result set very carefully because partition is applied on the work column. There are two sets here one is **Home** and other one is **Office**. When new partition starts you need to calculate it separately.

I hope this article is useful to you. Please leave your comment in the comment section below.

You can read related article SQL Date Functions here.