Why window functions are needed in SQL

Introduction

Window functions help us calculate values across rows related to the current row without losing the row's details. They make it easy to do running totals, ranks, and averages within groups.

When you want to find the rank of each student in a class without grouping all students into one row.

When you need a running total of sales for each day but still want to see each day's sales separately.

When you want to compare each employee's salary to the average salary in their department.

When you want to find the difference between a row's value and the previous row's value in a list.

When you want to calculate moving averages or cumulative sums in a report.

Syntax

SQL

SELECT column1, column2, window_function() OVER (PARTITION BY columnX ORDER BY columnY) AS alias_name FROM table_name;

The OVER() clause defines the window or set of rows the function works on.

PARTITION BY divides rows into groups; ORDER BY defines the order inside each group.

Examples

Ranks employees by salary from highest to lowest.

SQL

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

Calculates a running total of sales by date.

SQL

SELECT date, sales, SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total FROM daily_sales;

Shows each employee's salary and the average salary in their department.

SQL

SELECT department, employee, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees;

Sample Program

This query ranks employees by salary within their own department. It shows each employee's name, department, salary, and their rank in that department.

SQL

CREATE TABLE employees (id INT, name VARCHAR(20), department VARCHAR(20), salary INT);
INSERT INTO employees VALUES
(1, 'Alice', 'HR', 5000),
(2, 'Bob', 'HR', 6000),
(3, 'Charlie', 'IT', 7000),
(4, 'Diana', 'IT', 6500);

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

Important Notes

Window functions do not reduce the number of rows returned like GROUP BY does.

They allow you to keep row-level details while adding summary information.

Summary

Window functions let you perform calculations across related rows without grouping.

They are useful for rankings, running totals, and comparisons within groups.

They keep all rows visible while adding extra calculated columns.