SQL Window Functions

SQL Window Functions are powerful analytical functions that perform calculations across a set of related rows while preserving individual row details. Unlike aggregate functions that return a single result per group, SQL Window Functions return a value for every row. They are commonly used for ranking, running totals, moving averages, and advanced business reporting.

Sample Sales Data

SalesID	SalesPerson	Amount
1	Alice	700
2	Alice	700
3	Alice	500
4	Alice	500
5	Bob	600
6	Bob	400
7	Bob	400
8	Bob	300

SQL Query for Sample Data:

-- Base table used for all examples
DROP TABLE IF EXISTS Sales;
CREATE TABLE Sales (
  SalesID INT PRIMARY KEY,
  SalesPerson VARCHAR(50),
  Amount INT
);

INSERT INTO Sales (SalesID, SalesPerson, Amount) VALUES
(1, 'Alice', 700),
(2, 'Alice', 700),
(3, 'Alice', 500),
(4, 'Alice', 500),
(5, 'Bob',   600),
(6, 'Bob',   400),
(7, 'Bob',   400),
(8, 'Bob',   300);

1. RANK( )

The RANK() function assigns a unique rank to each row within a specified partition of a result set. The rank starts at 1 and increments for each row. If two or more rows have the same rank, the next rank will be skipped.

Syntax:

RANK() OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

Query:

SELECT
  SalesID, SalesPerson, Amount,
  RANK() OVER (
    PARTITION BY SalesPerson
    ORDER BY Amount DESC
  ) AS rnk
FROM Sales
ORDER BY SalesPerson, rnk, SalesID;

Output:

SalesID	SalesPerson	Amount	rnk
1	Alice	700	1
2	Alice	700	1
3	Alice	500	3
4	Alice	500	3
5	Bob	600	1
6	Bob	400	2
7	Bob	400	2
8	Bob	300	4

2. DENSE_RANK( )

The DENSE_RANK() function is similar to RANK(), but it does not skip rank numbers when they are the same rank in more than one row.

Syntax:

DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

Query:

SELECT
  SalesID, SalesPerson, Amount,
  DENSE_RANK() OVER (
    PARTITION BY SalesPerson
    ORDER BY Amount DESC
  ) AS dense_rnk
FROM Sales
ORDER BY SalesPerson, dense_rnk, SalesID;

Output:

SalesID	SalesPerson	Amount	dense_rnk
1	Alice	700	1
2	Alice	700	1
3	Alice	500	2
4	Alice	500	2
5	Bob	600	1
6	Bob	400	2
7	Bob	400	2
8	Bob	300	3

3. ROW_NUMBER()

The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition.

Syntax:

ROW_NUMBER() OVER ( PARTITION BY column_name
    ORDER BY column_name [ASC|DESC] )

Query:

SELECT
  SalesID, SalesPerson, Amount,
  ROW_NUMBER() OVER (
    PARTITION BY SalesPerson
    ORDER BY Amount DESC, SalesID ASC  -- tie-breaker for determinism
  ) AS rn
FROM Sales
ORDER BY SalesPerson, rn;

Output:

SalesID	SalesPerson	Amount	rn
1	Alice	700	1
2	Alice	700	2
3	Alice	500	3
4	Alice	500	4
5	Bob	600	1
6	Bob	400	2
7	Bob	400	3
8	Bob	300	4

4. NTILE( )

The NTILE() function evenly distributes the rows in an ordered partition into a specified number of groups, assigning a bucket number (1 through n) to each row.

Syntax:

NTILE(n) OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

Query:

SELECT
  SalesID, SalesPerson, Amount,
  NTILE(4) OVER (
    PARTITION BY SalesPerson
    ORDER BY Amount DESC, SalesID ASC
  ) AS bucket
FROM Sales
ORDER BY SalesPerson, bucket, SalesID;

Output:

SalesPerson	Amount	Bucket
Alice	700	1
Alice	500	2
Bob	400	1
Bob	300	2

5. LEAD( ) and LAG( )

• LEAD(): Returns the value of a specified column from the next row within the result set.
• LAG(): Returns the value of a specified column from the previous row within the result set.

Syntax:

LEAD(column_name) OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

LAG(column_name) OVER (PARTITION BY column_name ORDER BY column_name [ASC|DESC])

Query:

SELECT
  SalesID, SalesPerson, Amount,
  LAG(Amount)  OVER (ORDER BY Amount ASC, SalesID ASC) AS prev_amount,
  LEAD(Amount) OVER (ORDER BY Amount ASC, SalesID ASC) AS next_amount
FROM Sales
ORDER BY Amount ASC, SalesID ASC;

Output (first has NULL prev; last has NULL next):

SalesID	SalesPerson	Amount	prev_amount	next_amount
8	Bob	300	NULL	400
6	Bob	400	300	400
7	Bob	400	400	500
3	Alice	500	400	500
4	Alice	500	500	600
5	Bob	600	500	700
1	Alice	700	600	700
2	Alice	700	700	NULL

6. OVER( ) Clause

The OVER() clause in SQL is what transforms normal aggregate functions (like SUM, AVG, COUNT, RANK, etc.) into window functions meaning they operate across a set of rows (a window) without collapsing them into one result like GROUP BY does.

Syntax :

function() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column3 [ASC|DESC]
    ROWS or RANGE frame specification
)

Query: OVER() without PARTITION or ORDER

SELECT
  SalesPerson,
  Amount,
  SUM(Amount) OVER() AS TotalSales
FROM Sales;

Output:

SalesPerson	Amount	TotalSales
Alice	500	3600
Bob	400	3600
Alice	700	3600
Bob	300	3600
Carol	800	3600
Dan	900	3600

➡️ Here, OVER() means the entire table is the window —
every row “sees” the total of all rows.

Query: OVER(PARTITION BY ...) — Grouped Windows

SELECT
  SalesPerson,
  Amount,
  SUM(Amount) OVER(PARTITION BY SalesPerson) AS TotalPerPerson
FROM Sales;

Output:

SalesPerson	Amount	TotalPerPerson
Alice	500	1200
Alice	700	1200
Bob	400	700
Bob	300	700
Carol	800	800
Dan	900	900

➡️ The partition restricts the window to each salesperson’s rows.
Equivalent of GROUP BY, but still returns each row individually.

OVER(ORDER BY ...) — Ordered Windows

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

Output:

SalesPerson	Amount	RunningTotal
Bob	300	300
Bob	400	700
Alice	500	1200
Alice	700	1900
Carol	800	2700
Dan	900	3600

➡️ The ORDER BY defines sequence inside the window — often used for running totals, cumulative sums, or ranks.

OVER(PARTITION BY … ORDER BY …) — Combined

SELECT
  SalesPerson,
  Amount,
  SUM(Amount) OVER(
    PARTITION BY SalesPerson
    ORDER BY Amount
  ) AS RunningTotalPerPerson
FROM Sales;

Output:

SalesPerson	Amount	RunningTotalPerPerson
Alice	500	500
Alice	700	1200
Bob	300	300
Bob	400	700

➡️ Each partition (salesperson) has its own ordered running total.

Query: Using with Ranking Functions

OVER() is also used with analytic functions like RANK(), DENSE_RANK(), and ROW_NUMBER().

SELECT
  SalesPerson,
  Amount,
  RANK() OVER(PARTITION BY SalesPerson ORDER BY Amount DESC) AS RankInTeam
FROM Sales;

➡️ OVER() defines “how to rank” — by grouping and ordering logic.

Query: ROWS BETWEEN — Frame Specification

For fine control within the ordered window:

SELECT
  SalesPerson,
  Amount,
  AVG(Amount) OVER(
    ORDER BY Amount
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS MovingAvg
FROM Sales;

➡️ Calculates the average using the previous, current, and next row — a sliding window.

Summary of Over Clause

Clause Part	Purpose	Example
PARTITION BY	Divides data into groups	“Per SalesPerson”
ORDER BY	Defines order of rows in each group	“Sort by Amount”
ROWS / RANGE	Controls the frame (how many rows before/after are visible)	“Running or moving totals”

Conclusion

SQL Window Functions are essential for advanced data analysis and reporting. By mastering ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), and the SQL OVER Clause, you can create powerful analytical queries while maintaining row-level details. Understanding SQL Window Functions will significantly improve your SQL skills and help you solve complex business reporting challenges.

Knowledge Check

Frequently Asked Questions