SQL Windows Functions Explained like you are 5!

If you are an aspiring data analyst, you must learn SQL. If you are currently learning SQL now, YOU NEED TO LEARN WINDOWS FUNCTIONS IN SQL. If you understand these functions, it will make your life a lot easier whether it is to crack interviews or on the actual job itself.

So what are Windows Functions: — Summarize a particular table over a particular column(thus creating a window) and calculate the result using different functions(mentioned above)

The built-in SQL window functions are then sometimes divided into two different types: the ranking and value functions. The ranking window functions are used to simply assign numbers to the existing rows according to some requirements. On the other hand, the value window functions are used to copy values from other rows to other rows within the defined windows.

USE CASE — (Why you need to learn these):

From Tickertape

Suppose you have financial data of companies and you have recently started investing and you want to invest in companies now. You have the criteria or basic idea that you will only invest in companies that are in the Top 3 of their business category. (See image above). That’s where the importance of windows functions comes in. You will use the windows rank function(See the first image) and fetch on top 3 companies by the market cap of their business. (See the code below).

SELECT Name,
FROM
  (SELECT Name,
          Market Cap AS mcap,
          RANK() OVER (partition by subcategory ORDER BY mcap DESC) rnk
   FROM Table
   GROUP BY Name) a
WHERE rnk < 4

Ranking Window Functions :

Ranking functions are, RANK(), DENSE_RANK(), ROW_NUMBER()

• RANK() –
  As the name suggests, the rank function assigns a rank to all the rows within every partition. Rank is assigned such that rank 1 is given to the first row and rows having the same value are assigned the same rank. For the next rank after two same rank values, one rank value will be skipped.
• DENSE_RANK() –
  It assigns a rank to each row within a partition. Just like the rank function first row is assigned rank 1 and rows having the same value have the same rank. The difference between RANK() and DENSE_RANK() is that in DENSE_RANK(), for the next rank after two same ranks, the consecutive integer is used, and no rank is skipped.
• ROW_NUMBER() –
  It assigns consecutive integers to all the rows within a partition. Within a partition, no two rows can have the same row number.

From GeekforGeeks

Value Window Functions :

Value functions are, Lead() , Lag().

• LAG() — The LAG() function allows access to a value stored in a different row above the current row. The row above may be adjacent or some number of rows above, as sorted by a specified column or set of columns.

From Learn SQL.com

• LEAD() — Lead() is similar to Lag(). Whereas Lag() accesses a value stored in a row above, Lead() accesses a value stored in a row below.

From LearnSQl.com

As they say, clear the fundamentals and everything else can be learned along the way. This was written just to provide you with the basics of windows functions in SQL.

That’s It. I hope it helps you in your journey of becoming a #data analyst. I have used and used most of these websites not just for practice but for revision of multiple aspects of the data analytics journey.

If you have any questions or any inputs you want to share, just comment or hit me up on LinkedIn or Twitter. I am pretty much active there.

All the best.

Please Start. You will not regret it.

Thanks,

Piyush Jain