SQL Window Functions in 2 Minutes

What Are Window Functions?

Unlike standard aggregate functions like SUM() and AVG(), window functions don’t merge rows into a single result. Instead, they compute values across a specific "window" of rows using the OVER() clause.

Example: Ranking employees by salary

SELECT name, salary,  
RANK() OVER (ORDER BY salary DESC) AS salary_rank  
FROM employees;

Key Window Functions

Ranking Functions: RANK(), DENSE_RANK(), ROW_NUMBER()

Aggregation Functions: SUM(), AVG(), COUNT()

Offset Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Using PARTITION BY for Grouping

To calculate values within specific categories, use PARTITION BY:

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

This computes the average salary within each department while keeping individual row details intact.

Comparing Rows with LAG() & LEAD()

The LAG() and LEAD() functions allow you to access previous or next row values:

SELECT name, salary,  
LAG(salary) OVER (ORDER BY salary) AS prev_salary,  
LEAD(salary) OVER (ORDER BY salary) AS next_salary  
FROM employees;

Perfect for tracking salary changes over time!

Moving Averages & Running Totals

Use window frames (ROWS BETWEEN)to calculate rolling averages or running totals:

SELECT name, salary,  
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg  
FROM employees;

Use windows functions to:

✅ Maintain row-level details while computing aggregates
✅ Efficiently perform ranking, running totals, and comparisons
✅ Eliminate the need for complex self-joins and subqueries

Platform-Specific Considerations

1. General SQL Support

Most modern relational databases support window functions, but syntax and performance optimizations may differ:
✅ Supported: PostgreSQL, MySQL (8.0+), SQL Server (2012+), Oracle (11g+), IBM Db2
⚠️ Limited Support: MySQL (<8.0), SQLite (Partial)

2. Platform-Specific Optimizations & Differences

PostgreSQL

✅ Fully supports window functions with PARTITION BY, ORDER BY, and frame clauses.
✅ Strong optimizer for LAG(), LEAD(), and RANK().
⚠️ No parallelism for window functions (performance can degrade with large datasets).

MySQL (8.0+)

✅ First MySQL version to introduce full window function support.
✅ Supports ranking, offsets, and aggregates within OVER().
⚠️ No RANGE-based window frame (only ROWS).
⚠️ No DISTINCT inside window aggregates (e.g., COUNT(DISTINCT col) OVER (...) fails).

SQL Server (2012+)

✅ Full support for window functions and frame clauses.
✅ Optimized for parallel query execution with indexed partitions.
⚠️ Pre-2012 versions require workarounds (e.g., using CROSS APPLY for ranking).

Oracle (11g+)

✅ Supports advanced window functions like FIRST_VALUE(), LAST_VALUE().
✅ MODEL clause allows complex analytics beyond window functions.
⚠️ Requires explicit indexing for performance tuning.

IBM Db2

✅ Offers robust support for window functions.
✅ Can perform parallel processing of window functions.
⚠️ Some advanced analytics require custom extensions.

3. Performance Considerations

Indexing Matters: Ensure that columns used in PARTITION BY or ORDER BY are indexed.

Query Execution Plans: Use EXPLAIN ANALYZE (PostgreSQL), EXPLAIN FORMAT=JSON (MySQL), or SET SHOWPLAN_ALL ON (SQL Server) to check performance.