SQL WINDOW FUNCTIONS

In this article I will be showing some examples of WINDOW Functions in SQL.

These are used for ADVANCED ANALYTICAL QUERIES for getting accurate results/rank/Unique Values.

Some Window Functions are discussed here RANK(), DENSE_RANK(), ROW_NUMBER(), LEAD() and LAG().

Lets Explains this in briefly: -

For Example, I have student wise marks in the table as student_marks_tb

Syntax: - SELECT * from student_marks_tb;

Let’s see in Figure 1:

Figure 1: MySQL Example student_marks_tb

In this data, let’s assume we need to add rank to the student based on the marks scored.

— I have implemented RANK() function,

Syntax: - SELECT *, RANK() OVER (ORDER BY MARKS DESC) AS RNK FROM student_marks_tb;

Let's see result in the Figure 2:

Figure 2: Result Table using RANK()

>In this above fig 2, we are getting Ranks based on Marks.

>Here we are getting SAME RANK for RAJEV and GOPAL with 65 marks, like 6th RANK.

>And also, next rank 7 skipped and gives 8th RANK.

— For not getting Skipped/Gaps in ranks, we go for Next Window Function DENSE_RANK()

Syntax: - SELECT *, DENSE_RANK() OVER(ORDER BY MARKS DESC) AS RNK FROM student_marks_tb.

Let’s see in the Figure 3:

Figure 3: Result Table using DENSE_RANK()

>In this table we are getting Ranks based on Marks with no Skipping/Gaps in Ranks when there are ties.

>Like RANK() Function, we are not getting Skipped/Gap Rank found to next Rank. (Ranks will be Consecutive)

For this one (Dense_Rank) also I’m having one Problem, it gives Duplicate Ranks, If I want to give Every Unique Person as a UNIQUE RANK, then it will not work.

— So, we are going to another Window Function is ROW_NUMBER().

Syntax of ROW_NUMBER()

SELECT *, ROW_NUMBER() OVER(ORDER BY MARKS DESC) AS RNK FROM student_marks_tb;

Get Khuddus’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

let’s see Figure 4:

Figure 4: Result Table Using ROW_NUMBER()

As per above result we got 100% we got Unique Ranks as per Marks (Incase Marks are same in that condition it checks another possibility as per Registered ID, who registered First, for them they have given first preference (Example: RAJEV and GOPAL got 6th and 7th rank respectively as per they registered).

— Another Window Function is LAG(), that allows every company check their year on year growth sales. Let's take one Example Figure 5:

Figure 5: Table of Year-on-Year Sales

In this Table, we are having year and sales columns, in we can calculate the YOY sales by using LAG() function (in excel we can calculate YOY sales by using cell references as {[current year sales-previous year sales]/previous year sales }100 by this we are getting % of Growth Sales), Unfortunately in SQL, we are not having row level calculations, for this we are going add one more sales column as T_SALES.

Let's in this Figure 6,

Figure 6: Additional Column added as T_SALES

Now we can calculate year-on-year sales, by using LAG() window function.

Lag() means ONE STEP BELOW, with help of this function we are going to delay sales one step to newly added T_SALES column

Syntax: SELECT S_YEAR, T_SALES, LAG(T_SALES) OVER(ORDER BY S_YEAR) as PY_Sales FROM windows_function_lag_tb;

Let's see in Figure 7

Figure 7: PY_Sales get by using LAG() Window function

with help above Syntax, PY_Sales just now calculated so that we can write it as Subquery concept.

Syntax: SELECT *, ((T_SALES-PY_SALES)/PY_SALES)*100 as YOY_Growth FROM (SELECT S_YEAR, T_SALES, LAG(T_SALES) OVER(ORDER BY S_YEAR) as PY_SALES FROM windows_function_lag_tb) A;

We are getting YOY_Growth Sales result Table as below Figure 8,

Figure 8: Result Table show as YOY_Growth Sales.

With help of this Lag() function, we can calculate YOY_Growth Sales as compared with previous year sales.

— Let's go for another Window Function LEAD(), it gives ONE STEP UP.

For this Function, we take same table S_YEAR and T_SALES,

syntax: SELECT S_YEAR, T_SALES, LEAD(T_SALES) OVER(ORDER BY S_YEAR) AS Future_Sales FROM windows_function_lead_tb;

As shown in Figure 9,

Figure 9: Result Table of LEAD() Window Function

This Lead Function will be used for Compares present Sales with future sales like Stocks forecasting.

As per real time scenarios LAG() Function more frequently used as compared with LEAD() Function. Why because it compares with current data with past data.

Conclusion —

A simple attempt made to Understanding about SQL Window Functions with examples.