# Window Functions in SQL

A comprehensive article on basic window functions along with examples.

Window functions are similar to aggregate functions but they help us to concentrate on a window, not all the rows.

Window functions apply to aggregate and ranking functions over a particular window (set of rows)

**Basic Syntax**

SELECT coulmn_name1,

window_function(column_name2),

OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column

FROM table_name;window_function=any aggregate or ranking functioncolumn_name1= column to be selectedcoulmn_name2=column on which window function is to be appliedcolumn_name3= column on whose basis partition of rows is to be donenew_column=Name of new columntable_name=Name of table

**Note**

- Partitions rows into form set of rows. (PARTITION BY clause is used)
- Orders rows within those partitions into a particular order. (ORDER BY clause is used)
- If partitions aren’t done, then ORDER BY orders all rows of the table.

**Using Aggregate Functions as Window Functions**

Aggregate functions such as SUM(), COUNT(), AVERAGE(), MAX(), MIN() applied over a particular window (set of rows) are called aggregate window functions.

**Example**

To find the average salary of employees for each department and order employees within a department by age.

`SELECT Name, Age, Department, Salary, `

AVERAGE(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary

FROM employee

To Find Maximum Salary in each department

`select e.*, max(salary) over(partition by dept_name) as max_salary`

from employee e;

**row_number() window function**

It assigns consecutive integers to all the rows within a partition. Within a partition, no two rows can have the same row number.

**Examples**

Assign different row numbers to every row

`select e.*,`

row_number() over(partition by dept_name) as rn

from employee e;

To Fetch the first 2 employees from each department to join the company

`select * from (`

select e.*,

row_number() over(partition by dept_name order by emp_id) as rn

from employee e) x

where x.rn < 3;

**RANK() window function**

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.

**Example**

To Fetch the top 3 employees in each department earning the max salary.

`select * from (`

select e.*,

rank() over(partition by dept_name order by salary desc) as rnk

from employee e) x

where x.rnk < 4;

**DESNE_RANK() Window function**

Assigns rank to each row within a partition. Just like 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, no rank is skipped.

`select * from (`

select e.*,

dense_rank() over(partition by dept_name order by salary desc) as rnk

from employee e) x

where x.rnk < 4;

**Note**

row_number(),rank(),dense_rank() are rankfunctions.ORDER BY() should be specified compulsorily while using rank window functions.

In ROW_NUMBER() the row numbers are consecutive integers within each partition.

The difference between rank and dense rank is that in dense rank there is no gap between rank values while there is a gap in rank values after repeated rank.

**lead() and lag() window functions**

Lag() function is used to access previous rows data as per defined offset value.

Lead() function to access subsequent rows data as per defined offset value.

**Example**

To fetch a query to display if the salary of an employee is higher, lower, or equal to the previous employee.

`select e.*,`

lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal,

lead(salary) over(partition by dept_name order by emp_id) as next_empl_sal

from employee e;

There are a few other window functions like FIRST_VALUE, LAST_VALUE, etc. These will be covered sometime in the future. You can also check the reference links down below for the same.

If you found this article helpful. Consider clapping and follow me on medium.

Let's connect via Linkedin or Twitter for any further discussions on big data and ML

**References**