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.

DROP TABLE IF EXISTS Employees;
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);

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);

Database Overview & Sample Data

The database is pre-loaded with the Sales and Employees tables. Run the queries below to explore the data.

Sales Table

SELECT * FROM Sales;

Employees Table

SELECT * FROM Employees;

Running Total

Operation: Calculate a running total of sales amounts over time.

Query:

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

Result:

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: