In SQL, a **window function** is a function that performs a calculation across a set of rows that are related to the current row. These functions operate on a set of rows called a window and return a single value for each row in the result set.

Window functions can be used in the SELECT, FROM, and WHERE clauses of a SELECT statement. They are often used to calculate a running total or moving average or to rank rows within a result set.

#### Employee Table

emp_id | emp_name | salary | dept_id | manager_id |
---|---|---|---|---|

E1 | Rahul | 15000 | D1 | M1 |

E2 | Manoj | 15000 | D1 | M1 |

E3 | James | 55000 | D2 | M2 |

E4 | Michael | 25000 | D2 | M2 |

E5 | Ali | 20000 | D10 | M3 |

E6 | Robin | 35000 | D10 | M3 |

Here are some examples of how you could use SQL window functions on the above dataset:

**RANK() function:**

You can use the `RANK()`

function to assign a rank to each employee based on their salary, with the highest salary receiving a rank of 1. For example:

```
SELECT emp_id, emp_name, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM Employee;
```

#### Output

emp_id | emp_name | salary | salary_rank |
---|---|---|---|

E3 | James | 55000 | 1 |

E6 | Robin | 35000 | 2 |

E4 | Michael | 25000 | 3 |

E5 | Ali | 20000 | 4 |

E1 | Rahul | 15000 | 5 |

E2 | Manoj | 15000 | 5 |

**LAG() function:**

You can use the `LAG()`

function to retrieve the salary of the previous employee in the list. For example:

```
SELECT emp_id, emp_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) as prev_salary
FROM Employee;
```

#### Output

emp_id | emp_name | salary | prev_salary |
---|---|---|---|

E1 | Rahul | 15000 | |

E2 | Manoj | 15000 | 15000 |

E5 | Ali | 20000 | 15000 |

E4 | Michael | 25000 | 20000 |

E6 | Robin | 35000 | 25000 |

E3 | James | 55000 | 35000 |

**SUM() function:**

You can use the `SUM()`

function to calculate the total salary of all employees in each department. For example:

```
SELECT distinct dept_id, SUM(salary) OVER (PARTITION BY dept_id) as dept_total_salary
FROM Employee;
```

#### Output

dept_id | dept_total_salary |
---|---|

D1 | 30000 |

D10 | 55000 |

D2 | 80000 |

In this example, the `SUM()`

function is used as a window function to calculate the running total of the `value`

column. The `OVER`

clause specifies the window over which the function operates, in this case, the set of rows ordered by the `id`

column. The result of the query would be a list of rows with the `id`

that are distinct in the table, `value`

, and `running_total`

columns. The `running_total`

column would contain the sum of all the `value`

rows up to and including the current row, ordered by `id`

.

There are many other window functions available in SQL, including `AVG()`

, `COUNT()`

, `MAX()`

, `MIN()`

, and `RANK()`

. You can find more information about window functions in the documentation for your specific database management system.

## Code Runner

#### Copy the below queries in the code runner to see the result

```
SELECT emp_id, emp_name, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM Employee;
```

```
SELECT emp_id, emp_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) as prev_salary
FROM Employee;
```

```
SELECT distinct dept_id, SUM(salary) OVER (PARTITION BY dept_id) as dept_total_salary
FROM Employee;
```