Window Functions

In the previous lesson, we learned about Cursors, which process data row by row.

In this lesson, we introduce Window Functions, which allow advanced analytics without sacrificing performance.

Window functions operate on a set of rows related to the current row — called a window.

What is a Window Function?

A window function performs a calculation across a group of rows while still returning individual rows.

Unlike GROUP BY:

• Rows are not collapsed
• Original row detail is preserved
• Aggregates coexist with row-level data

Basic Window Function Syntax

function_name(column)
OVER (
  PARTITION BY column
  ORDER BY column
);
  

The OVER() clause defines the window.

Example Table

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | HR         | 65000  |
| 5  | Evan     | Finance    | 80000  |
+----+----------+------------+--------+
  

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a partition.

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

Each department starts numbering from 1.

RANK()

RANK() assigns rankings, but allows gaps when values are tied.

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

DENSE_RANK()

DENSE_RANK() is similar to RANK, but does not create gaps.

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

ROW_NUMBER vs RANK vs DENSE_RANK

Aggregate Functions as Window Functions

Aggregate functions can be used with OVER().

Example: Department average salary shown per employee.

SELECT name, department, salary,
       AVG(salary) OVER (
         PARTITION BY department
       ) AS dept_avg_salary
FROM employees;
  

This would be impossible with GROUP BY alone.

Running Total (Cumulative Sum)

Calculate running salary total by department.

SELECT name, department, salary,
       SUM(salary) OVER (
         PARTITION BY department
         ORDER BY salary
       ) AS running_total
FROM employees;
  

PARTITION BY vs GROUP BY

• GROUP BY → reduces rows
• PARTITION BY → preserves rows

Window functions are ideal for analytics.

When Should You Use Window Functions?

Use window functions when:

• You need rankings or analytics
• You need totals alongside row data
• You want high performance

Window Functions vs Cursors

Common Beginner Mistakes

• Forgetting OVER() clause
• Confusing GROUP BY with PARTITION BY
• Using cursors instead of window functions

Why Window Functions Matter

Window functions are essential for:

• Analytics queries
• Reporting dashboards
• Data science workloads

They are a must-have skill for SQL professionals.

What’s Next?

In the next lesson, we will explore Recursive Queries, used for hierarchical and tree-structured data.