SQL: WINDOW FUNCTIONS

1. What are Window Functions?

A window function performs an analytical calculation across a set of table rows that are related to the current row. Unlike a traditional aggregate query with GROUP BY, a window function does not collapse multiple rows into a single summary record. Each input row retains its distinct identity, while receiving calculated attributes computed over its analytical window.

Standard SQL defines window functions using the OVER clause. This enables side-by-side comparisons of individual records against group aggregates, such as showing each employee salary next to the company average or ranking student exam results within each classroom.

2. The OVER Clause

The OVER clause is the syntax marker that transforms an aggregate or ranking function into a window function. It defines the subset of rows (the window) that the function inspects for each individual row.

An empty OVER () clause instructs SQL to treat the entire filtered result set as a single window. Every row receives the grand total or overall average alongside its individual fields without requiring a separate subquery or self-join.

3. PARTITION BY

The PARTITION BY subclause divides the query result set into isolated groups called partitions. The window function calculates values independently within each partition, resetting its counters and accumulators as boundary values transition.

Unlike GROUP BY, dividing rows into partitions does not reduce row counts. If department 1 has 3 employees, all 3 employees appear in the output, each carrying the summary metrics calculated specifically for department 1.

4. ORDER BY inside OVER

An ORDER BY clause placed inside the OVER (...) parenthesis specifies the sequence in which rows are processed within each partition. This inner ordering is distinct from the final query-level ORDER BY that formats the returned table.

The inner order dictates how ranking functions assign positions, which row counts as first or last, and how running totals accumulate values from preceding rows.

5. ROW_NUMBER()

The ROW_NUMBER() function returns a unique, strictly incrementing integer (1, 2, 3...) for each row within its partition, according to the specified ORDER BY clause.

ROW_NUMBER() never assigns duplicate values within the same partition. Even when two rows share identical values in the sort column, each row receives an incremented number based on internal tie-breaking or physical row placement.

6. RANK() and DENSE_RANK()

When items share the same numerical value, business requirements often demand equal ranking. SQL provides two distinct ranking functions for handling ties:

• RANK(): Assigns identical rank numbers to tied values, but skips subsequent rank numbers according to the number of ties (for example: 1, 2, 2, 4).
• DENSE_RANK(): Assigns identical rank numbers to tied values, but never leaves gaps in the numeric sequence (for example: 1, 2, 2, 3).

7. Comparison: RANK vs DENSE_RANK vs ROW_NUMBER

Understanding the distinction between these three ranking functions is essential for accurate analytical queries and pagination routines.

8. NTILE()

The NTILE(n) function divides an ordered partition into n roughly equal buckets and assigns the bucket number (1 through n) to each row. Common use cases include calculating quartiles (NTILE(4)), deciles (NTILE(10)), or percentiles.

If the row count is not evenly divisible by n, the remainder rows are distributed starting from bucket 1 downward. For example, 7 rows partitioned into 3 buckets results in buckets of size 3, 2, and 2.

9. LAG() and LEAD()

LAG() and LEAD() are positional navigation functions that allow a query to look backwards or forwards relative to the current row without constructing expensive self-joins.

• LAG(col, offset, default): Fetches the value from offset rows prior to the current row (defaults to offset 1 and NULL).
• LEAD(col, offset, default): Fetches the value from offset rows following the current row (defaults to offset 1 and NULL).

10. FIRST_VALUE, LAST_VALUE, and NTH_VALUE

These value navigation functions return specific edge or index entries from an ordered window:

• FIRST_VALUE(col): Returns the value from the first row of the window frame.
• LAST_VALUE(col): Returns the value from the last row of the window frame.
• NTH_VALUE(col, n): Returns the value from the n-th row of the window frame.

11. Running Total (Cumulative Sum)

A running total calculates the progressive accumulation of values over time or order sequence. Adding an ORDER BY clause inside an aggregate window function automatically converts it into a running calculation.

12. Moving Average

A moving average smooths short-term fluctuations to reveal underlying trends. It computes the mean over a sliding window frame consisting of a fixed number of rows surrounding the current record.

13. Window Frames: ROWS BETWEEN and RANGE BETWEEN

A window frame defines the exact slice of rows inside a partition that participates in the calculation for the current row. Frame bounds use five positional qualifiers:

• UNBOUNDED PRECEDING: Starts at the very first row of the partition.
• n PRECEDING: Starts n rows before the current row.
• CURRENT ROW: The current row being evaluated.
• n FOLLOWING: Extends n rows after the current row.
• UNBOUNDED FOLLOWING: Extends to the very last row of the partition.

14. Aggregate Functions as Window Functions

Standard aggregate functions - SUM, AVG, MIN, MAX, and COUNT - transform into window functions by appending an OVER clause. This allows developers to express ratios and percentages without subqueries.

15. GROUP BY vs PARTITION BY

While both operations segment data by categorical attributes, their architectural mechanics and output shapes differ fundamentally:

16. Top N per Group

A frequent analytical challenge is finding the top N records within each category (such as the top 2 highest earners in each department).

Because window functions run during the SELECT phase after the WHERE clause has already completed, you cannot filter on window functions directly in a WHERE clause. The standard SQL pattern solves this by computing the ranking inside a Common Table Expression (CTE) or derived table subquery, and then applying the filter in the outer query.