<iframe src="https://devtoolbox.dedyn.io/tools/sql-window-function-builder" width="100%" height="700" frameborder="0" title="SQL Window Function Builder"></iframe>
SQL Window Function Builder
Visually build SQL window functions with PARTITION BY, ORDER BY, and frame clauses. Generate ROW_NUMBER, RANK, running totals, moving averages, and more.
Preset Examples
Select Expression & Table
Window Function
PARTITION BY
ORDER BY
Frame Specification (optional)
Named Window (optional)
Alias
Generated SQL
Frequently Asked Questions
What is a SQL window function?▼
A SQL window function performs a calculation across a set of rows related to the current row, called a "window." Unlike GROUP BY aggregates, window functions do not collapse rows -- each row keeps its identity while gaining access to computed values. Common examples include ROW_NUMBER(), RANK(), SUM() OVER, LAG(), and LEAD().
What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?▼
ROW_NUMBER() assigns a unique sequential integer to each row. RANK() assigns the same rank to tied rows but leaves gaps (1, 2, 2, 4). DENSE_RANK() assigns the same rank to tied rows without gaps (1, 2, 2, 3). Use ROW_NUMBER for unique numbering, RANK for competition-style ranking, and DENSE_RANK when you need no gaps.
How does PARTITION BY work in window functions?▼
PARTITION BY divides the result set into groups, and the window function is applied independently to each partition. For example, RANK() OVER (PARTITION BY department ORDER BY salary DESC) ranks employees within each department separately. Without PARTITION BY, all rows are treated as one partition.
What is a window frame clause (ROWS BETWEEN)?▼
A window frame clause defines which rows within the partition the function operates on. The syntax is ROWS|RANGE|GROUPS BETWEEN start AND end. Common frames include ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running total), ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING (5-row moving average), and ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (entire partition).
How do LAG and LEAD window functions work?▼
LAG() accesses data from a previous row without a self-join. LEAD() accesses data from a subsequent row. Both accept three arguments: the column, the offset (default 1), and a default value if the offset goes beyond the partition. For example, LAG(sales, 1, 0) OVER (ORDER BY month) returns the previous month's sales, or 0 if there is no previous row.