Window Functions

Aggregate Functions (Used for calculations over a set of rows)

• SUM(): Calculates the sum of values over a window.

• AVG(): Calculates the average of values.

• MIN(): Finds the minimum value.

• MAX(): Finds the maximum value.

• COUNT(): Counts rows over a window.

Example: Calculate the Total Sales by Department for Each Employee

SELECT 
  employee_id, 
  department_id, 
  salary, 
  SUM(salary) OVER (PARTITION BY department_id) AS total_department_salary 
FROM employees;

Ranking Functions (Used to rank rows within a partition)

• RANK(): Assigns a rank with gaps in case of ties.

• DENSE_RANK(): Assigns ranks without gaps.

• ROW NUMBER(): Assigns a unique sequential number to each row.

• NTILE(): Divides rows into buckets and assigns bucket numbers.

Example: Rank Employees Based on Salary within Their Department

SELECT 
  employee_id, 
  department_id, 
  salary, 
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank 
FROM employees;

Value Functions (Used to retrieve values from different rows)

• LAG(): Returns the value from the previous row.

• LEAD(): Returns the value from the next row.

• FIRST_VALUE(): Returns the first value in the window.

• LAST_VALUE(): Returns the last value in the window.

• NTH_VALUE(): Returns the nth value from the window.

Example: Calculate the Difference in Salary Between Consecutive Employees

SELECT 
  employee_id, 
  salary, 
  LAG(salary) OVER (ORDER BY salary) AS previous_salary, 
  salary - LAG (salary) OVER (ORDER BY salary) AS salary_difference 
FROM employees;