PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

Window Functions

Perform relative partition operations using ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD window frames.

Relational Theory & Optimization Architecture

Window functions perform analytical calculations across a set of table rows that are related to the current row, without collapsing the individual rows into a single summary output. Unlike GROUP BY, which reduces granularity, window functions preserve all underlying row details while appending computed metrics (such as running totals, rankings, moving averages, and lead/lag differences).

Physical Storage & Buffer Pool Mechanics

During query execution, window functions are evaluated in the final processing phase, immediately after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT. The engine partitions the working relation into independent windows based on PARTITION BY keys, sorts each partition according to ORDER BY specifications, and applies the window framing specification (ROWS or RANGE BETWEEN) to compute values.

Window Partitioning and Framing Mechanics

The window framing clause (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) defines the dynamic boundary of rows evaluated for each record. By default, specifying an ORDER BY inside an OVER() clause implies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Understanding the distinction between physical row frames (ROWS) and logical value ranges (RANGE) is crucial for accurate running totals with duplicate values.

Worked Query Walkthrough: Departmental Salary Rankings and Running Totals

SELECT employee_id, department_id, salary,
       ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
       DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk,
       SUM(salary) OVER(PARTITION BY department_id ORDER BY salary DESC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_dept_salary
FROM employees;

Step-by-Step Row Evaluation Matrix

employee_iddepartment_idsalaryrow_numdense_rnkrunning_dept_salaryFraming Mechanism
101Engineering15000011150000First row of Engineering partition
102Engineering13000022280000Sum of row 1 + row 2
103Engineering13000032 (Tie)410000Sum of rows 1 through 3
104Marketing950001195000Partition resets for Marketing

Runtime Trace Analysis

Employees 102 and 103 share the same salary ($130,000). ROW_NUMBER strictly assigns distinct sequential integers (2 and 3), while DENSE_RANK assigns both the rank of 2 without creating a gap. The running sum accumulates salaries strictly within each department partition.

Common Traps, Anti-Patterns & Debugging Solutions

1. Attempting to Filter Window Functions in the WHERE Clause

Problematic Syntax (Anti-Pattern)SELECT name, salary, RANK() OVER(ORDER BY salary DESC) as rnk FROM employees WHERE rnk <= 3;
Optimized AlternativeWITH Ranked AS (SELECT name, salary, RANK() OVER(ORDER BY salary DESC) as rnk FROM employees) SELECT * FROM Ranked WHERE rnk <= 3;

Window functions are evaluated after the WHERE clause. The alias 'rnk' does not exist when WHERE runs. Filtering window metrics requires wrapping the query in a CTE or subquery.

2. Confusing RANK(), DENSE_RANK(), and ROW_NUMBER()

Problematic Syntax (Anti-Pattern)-- Expecting consecutive integers without gaps on tied values using RANK()
Optimized AlternativeDENSE_RANK() OVER(ORDER BY score DESC)

RANK() skips numbers following ties (e.g. 1, 2, 2, 4). DENSE_RANK() does not skip numbers (1, 2, 2, 3). ROW_NUMBER() assigns distinct sequential integers regardless of ties (1, 2, 3, 4).

3. Default RANGE Frame Traps with Running Totals

Problematic Syntax (Anti-Pattern)SUM(amount) OVER(ORDER BY transaction_date) -- Defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Optimized AlternativeSUM(amount) OVER(ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

RANGE considers all duplicate values of transaction_date together, adding all identical dates simultaneously instead of step-by-step. ROWS forces strict physical row-by-row accumulation.

Production Strategy & Query Engineering Best Practices

Window functions are essential for production analytics. Key patterns include finding top-N elements per category using DENSE_RANK(), calculating period-over-period growth using LAG(), and identifying streaks or session thresholds.

Interactive WebAssembly SQL Playground

Client-Side SQLite Engine
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
EmployeeEmpID, Name, DeptID, Salary, ManagerID8 rowsStaff directory with salaries and reporting structure
DepartmentDeptID, DeptName, Budget8 rowsOrganizational departments and annual budgets
ProjectProjectID, ProjectName, DeptID, Deadline5 rowsActive enterprise engineering projects
Interactive Query Workspace

Run custom SQL queries against the Employee, Department, Project schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.

Frequently Asked Questions on Window Functions

Can window functions be placed in WHERE or HAVING clauses?

No. Window functions execute after WHERE, GROUP BY, and HAVING in logical query processing. To filter based on a window function result, wrap the calculation in a CTE or subquery.

What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

ROW_NUMBER assigns unique sequential integers. RANK assigns identical numbers to ties and skips subsequent ranks. DENSE_RANK assigns identical numbers to ties without skipping subsequent ranks.

How do LEAD() and LAG() navigate partition rows?

LAG(column, offset) accesses values from preceding rows within the partition, while LEAD(column, offset) accesses subsequent rows, allowing delta and time-lapse calculations without self-joins.

What is the performance advantage of window functions over self-joins?

Window functions compute rankings and running metrics in a single pass (O(N log N) sorting cost) rather than performing an O(N^2) Cartesian self-join across the dataset.

Explore Other SQL Practice Tracks