Window functions in SQL

Window functions are a type of function in SQL that allow you to perform a calculation over a set of rows that are related to the current row. They are commonly used with the OVER clause, which defines the set of rows that the function operates on.

Here are some examples of common window functions in SQL:

RANK(): This function assigns a rank to each row within a result set, with 1 being the highest rank. For example, the following query returns the rank of each employee based on their salary:

SELECT employee_name, salary,       RANK() OVER (ORDER BY salary DESC) as salary_rankFROM employees;

DENSE_RANK(): This function works similarly to RANK(), but it does not leave gaps in the ranking. In other words, if two rows have the same rank, they will both receive the same rank value, and the next rank will be incremented by 2 rather than 1.

NTILE(): This function divides the rows in a result set into a specified number of groups, or "tiles." It can be useful for dividing data into equal groups for further analysis. For example, the following query divides the employees into 4 tiles based on their salary:

SELECT employee_name, salary,       NTILE(4) OVER (ORDER BY salary DESC) as salary_tileFROM employees;

ROW_NUMBER(): This function assigns a unique number to each row within a result set, starting at 1. For example, the following query assigns a row number to each employee based on their hire date:

SELECT employee_name, hire_date,       ROW_NUMBER() OVER (ORDER BY hire_date ASC) as hire_date_rankFROM employees;

AVG(): This function calculates the average value of a set of rows. For example, the following query returns the average salary of all employees:

SELECT AVG(salary) OVER () as average_salaryFROM employees;

These are just a few examples of the types of window functions that are available in SQL. There are many others, including functions for calculating sums, minimums, maximums, and more.

I hope it helps!

Thanks and follow for more content.