SQL Window Functions

Window Functions perform a calculation across a set of table rows that are somehow related to the current row.

Unlike standard aggregate functions (like SUM() or COUNT()), window functions do not collapse rows into a single result. Instead, they keep the original rows and add a calculated value to each of them.

Syntax

The key to window functions is the OVER() clause.

Database Preparation

Run this block to set up the tables used in the examples below.

DROP TABLE IF EXISTS Sales;
CREATE TABLE Sales (
    Date VARCHAR(20),
    Amount DECIMAL(10, 2)
);
INSERT INTO Sales (Date, Amount)
VALUES ('Jan 1', 100), ('Jan 2', 200), ('Jan 3', 150);

DROP TABLE IF EXISTS Employees;
CREATE TABLE Employees (
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);
INSERT INTO Employees (Name, Department, Salary)
VALUES ('Alice', 'HR', 5000), ('Bob', 'HR', 6000), ('Charlie', 'IT', 8000);

SELECT 'Database Ready' AS Status;

Running Total

Query:

SELECT 
    Date, 
    Amount, 
    SUM(Amount) OVER (ORDER BY Date) AS RunningTotal
FROM Sales;

PARTITION BY

The PARTITION BY clause divides the result set into partitions (groups) and restarts the calculation for each group.

Operation: Calculate the average salary per department, but display it next to every employee so we can compare their salary to their department's average.

SELECT 
    Name, 
    Department, 
    Salary, 
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary
FROM Employees;

Result:

Ranking Functions

Window functions are excellent for ranking data.

• ROW_NUMBER(): Assigns a unique integer (1, 2, 3...) to rows.
• RANK(): Assigns a rank (1, 2, 2, 4...) with gaps for ties.
• DENSE_RANK(): Assigns a rank (1, 2, 2, 3...) without gaps.

Example: Rank employees by salary

SELECT 
    Name, 
    Salary, 
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Hands-on Exercise

Task: Use a window function to assign a row number to each employee, ordered by their Salary in descending order.

Your Query:

-- Write your query here

Expected Results: