SQL Window Functions: The Complete Guide for 2026
Window functions are one of SQL's most powerful features. They let you perform calculations across a set of rows that are related to the current row, without collapsing those rows into a single output like GROUP BY does. This means you can add running totals, rankings, moving averages, and row comparisons right alongside your detail data in a single query.
If you have ever written a self-join or correlated subquery just to compare a row to its neighbors, or used application code to compute a running total that the database could have done for you, window functions will change how you write SQL. This guide covers everything from basic syntax to advanced frame specifications, with practical examples you can run in PostgreSQL, MySQL 8+, and SQLite 3.25+.
Table of Contents
- Sample Data
- What Are Window Functions
- The OVER() Clause and PARTITION BY
- ORDER BY Within Windows
- ROW_NUMBER, RANK, DENSE_RANK
- NTILE for Bucketing
- LAG and LEAD
- FIRST_VALUE, LAST_VALUE, NTH_VALUE
- SUM, AVG, COUNT as Window Functions
- ROWS vs RANGE Frame Specifications
- GROUPS Frame (SQL:2011)
- Named Windows (WINDOW Clause)
- Common Patterns
- Performance Considerations
- Database-Specific Support
- Frequently Asked Questions
Sample Data
Every example in this guide uses the following sales data. This schema is simple enough to follow but realistic enough to demonstrate all window function features.
CREATE TABLE sales (
id INT PRIMARY KEY,
employee VARCHAR(50),
department VARCHAR(50),
amount DECIMAL(10,2),
sale_date DATE
);
INSERT INTO sales VALUES
(1, 'Alice', 'Engineering', 1200.00, '2026-01-05'),
(2, 'Alice', 'Engineering', 1800.00, '2026-01-12'),
(3, 'Alice', 'Engineering', 1500.00, '2026-01-19'),
(4, 'Bob', 'Engineering', 2200.00, '2026-01-08'),
(5, 'Bob', 'Engineering', 2200.00, '2026-01-15'),
(6, 'Charlie', 'Marketing', 900.00, '2026-01-03'),
(7, 'Charlie', 'Marketing', 1100.00, '2026-01-10'),
(8, 'Diana', 'Marketing', 1400.00, '2026-01-07'),
(9, 'Diana', 'Marketing', 1600.00, '2026-01-14'),
(10, 'Diana', 'Marketing', 1300.00, '2026-01-21');What Are Window Functions
A window function performs a calculation across a "window" of rows related to the current row. Unlike GROUP BY, which collapses many rows into one, a window function keeps every row in the output and adds the computed value as a new column.
-- GROUP BY: collapses rows (3 Alice rows become 1)
SELECT employee, SUM(amount) AS total
FROM sales
GROUP BY employee;
/* Alice | 4500.00
Bob | 4400.00
... */
-- Window function: keeps all rows, adds total alongside each
SELECT employee, amount, sale_date,
SUM(amount) OVER (PARTITION BY employee) AS emp_total
FROM sales;
/* Alice | 1200.00 | 2026-01-05 | 4500.00
Alice | 1800.00 | 2026-01-12 | 4500.00
Alice | 1500.00 | 2026-01-19 | 4500.00
Bob | 2200.00 | 2026-01-08 | 4400.00
... */Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT. This means you cannot use a window function in a WHERE clause directly. Instead, wrap the query in a CTE or subquery.
The OVER() Clause and PARTITION BY
Every window function requires an OVER() clause. This is what distinguishes a window function call from a regular aggregate. The OVER clause defines which rows constitute the "window" for the calculation.
-- Empty OVER(): the entire result set is the window
SELECT employee, amount,
SUM(amount) OVER () AS grand_total
FROM sales;
-- Every row shows the same grand_total: 15200.00
-- PARTITION BY: divides rows into groups (like GROUP BY, but keeps all rows)
SELECT employee, department, amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM sales;
/* Alice | Engineering | 1200.00 | 8900.00
Alice | Engineering | 1800.00 | 8900.00
Bob | Engineering | 2200.00 | 8900.00
Charlie | Marketing | 900.00 | 6300.00
Diana | Marketing | 1400.00 | 6300.00
... */
-- Multiple PARTITION BY columns
SELECT employee, department, amount,
COUNT(*) OVER (PARTITION BY department, employee) AS sales_count
FROM sales;Think of PARTITION BY as creating independent "sub-windows." The function resets and recalculates for each partition. If you omit PARTITION BY, the entire result set is one partition.
ORDER BY Within Windows
Adding ORDER BY inside OVER() does two things: it defines the order of rows within each partition, and for aggregate window functions it implicitly sets a frame from the start of the partition to the current row.
-- Without ORDER BY: SUM covers the entire partition
SELECT employee, sale_date, amount,
SUM(amount) OVER (PARTITION BY employee) AS emp_total
FROM sales WHERE employee = 'Alice';
/* Alice | 2026-01-05 | 1200.00 | 4500.00
Alice | 2026-01-12 | 1800.00 | 4500.00
Alice | 2026-01-19 | 1500.00 | 4500.00 */
-- With ORDER BY: SUM becomes a running total
SELECT employee, sale_date, amount,
SUM(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS running_total
FROM sales WHERE employee = 'Alice';
/* Alice | 2026-01-05 | 1200.00 | 1200.00 (1200)
Alice | 2026-01-12 | 1800.00 | 3000.00 (1200 + 1800)
Alice | 2026-01-19 | 1500.00 | 4500.00 (1200 + 1800 + 1500) */This implicit frame behavior is the source of many surprises. When you add ORDER BY to a window aggregate, the default frame changes from "all rows in partition" to "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW." We will cover frames in detail later.
ROW_NUMBER, RANK, DENSE_RANK
These three functions assign a position number to each row within its partition. They differ in how they handle ties (rows with equal ORDER BY values).
SELECT employee, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
RANK() OVER (ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales;
/* employee | amount | row_num | rnk | dense_rnk
---------+---------+---------+-----+----------
Bob | 2200.00 | 1 | 1 | 1
Bob | 2200.00 | 2 | 1 | 1 <-- tie
Alice | 1800.00 | 3 | 3 | 2 <-- RANK skips 2, DENSE_RANK does not
Alice | 1500.00 | 4 | 4 | 3
Diana | 1600.00 | 5 | 5 | 4
Diana | 1400.00 | 6 | 6 | 5
Diana | 1300.00 | 7 | 7 | 6
Alice | 1200.00 | 8 | 8 | 7
Charlie | 1100.00 | 9 | 9 | 8
Charlie | 900.00 | 10 | 10 | 9 */Top-N per group with ROW_NUMBER
-- Get each department's highest sale
WITH ranked AS (
SELECT employee, department, amount, sale_date,
ROW_NUMBER() OVER (
PARTITION BY department ORDER BY amount DESC
) AS rn
FROM sales
)
SELECT employee, department, amount, sale_date
FROM ranked
WHERE rn = 1;
/* Bob | Engineering | 2200.00 | 2026-01-08
Diana | Marketing | 1600.00 | 2026-01-14 */ROW_NUMBER is deterministic only when the ORDER BY uniquely identifies each row. If there are ties, the assignment among tied rows is arbitrary. Add a tiebreaker column (like the primary key) if you need consistent results.
NTILE for Bucketing
NTILE(n) distributes rows into n roughly equal-sized buckets. It is useful for percentile calculations and segmentation.
-- Split all sales into 4 quartiles by amount
SELECT employee, amount,
NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;
/* employee | amount | quartile
---------+---------+---------
Charlie | 900.00 | 1 (bottom 25%)
Charlie | 1100.00 | 1
Alice | 1200.00 | 1
Diana | 1300.00 | 2
Diana | 1400.00 | 2
Alice | 1500.00 | 2
Diana | 1600.00 | 3
Alice | 1800.00 | 3
Bob | 2200.00 | 4 (top 25%)
Bob | 2200.00 | 4 */
-- Segment employees by performance within their department
SELECT employee, department, amount,
NTILE(3) OVER (PARTITION BY department ORDER BY amount DESC) AS tier
FROM sales;If the number of rows is not evenly divisible by n, the first buckets get one extra row each. For 10 rows with NTILE(4): buckets get 3, 3, 2, 2 rows respectively.
LAG and LEAD
LAG accesses a row before the current row; LEAD accesses a row after. Both accept an offset (default 1) and a default value for when there is no row at that offset.
-- Compare each sale to the previous sale for the same employee
SELECT employee, sale_date, amount,
LAG(amount, 1, 0) OVER (
PARTITION BY employee ORDER BY sale_date
) AS prev_amount,
amount - LAG(amount, 1, 0) OVER (
PARTITION BY employee ORDER BY sale_date
) AS change
FROM sales;
/* employee | sale_date | amount | prev_amount | change
---------+------------+---------+-------------+--------
Alice | 2026-01-05 | 1200.00 | 0.00 | 1200.00 (no prev, default 0)
Alice | 2026-01-12 | 1800.00 | 1200.00 | 600.00
Alice | 2026-01-19 | 1500.00 | 1800.00 | -300.00
Bob | 2026-01-08 | 2200.00 | 0.00 | 2200.00
... */
-- Look ahead: compare to next sale
SELECT employee, sale_date, amount,
LEAD(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS next_amount
FROM sales;
-- Calculate month-over-month growth rate
SELECT sale_month, revenue,
LAG(revenue) OVER (ORDER BY sale_month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY sale_month))
/ LAG(revenue) OVER (ORDER BY sale_month), 1) AS growth_pct
FROM monthly_revenue;FIRST_VALUE, LAST_VALUE, NTH_VALUE
These functions return a specific value from the window frame. Be careful with LAST_VALUE: the default frame often does not include the row you expect.
-- Compare every sale to the employee's first and best sale
SELECT employee, sale_date, amount,
FIRST_VALUE(amount) OVER (
PARTITION BY employee ORDER BY sale_date
) AS first_sale,
FIRST_VALUE(amount) OVER (
PARTITION BY employee ORDER BY amount DESC
) AS best_sale
FROM sales;
-- LAST_VALUE gotcha: the default frame ends at CURRENT ROW
-- so LAST_VALUE only sees rows up to the current position
SELECT employee, sale_date, amount,
LAST_VALUE(amount) OVER (
PARTITION BY employee ORDER BY sale_date
-- Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS last_so_far -- NOT the actual last value!
FROM sales;
-- Fix: extend the frame to the end of the partition
SELECT employee, sale_date, amount,
LAST_VALUE(amount) OVER (
PARTITION BY employee ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS actual_last_sale
FROM sales;
-- NTH_VALUE: get the Nth row's value from the frame
SELECT employee, sale_date, amount,
NTH_VALUE(amount, 2) OVER (
PARTITION BY employee ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_sale
FROM sales;Key point: FIRST_VALUE works intuitively because the frame always starts at the beginning of the partition. LAST_VALUE and NTH_VALUE require an explicit frame that extends to UNBOUNDED FOLLOWING to see all rows in the partition.
SUM, AVG, COUNT as Window Functions
Any standard aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function by adding an OVER clause. This gives you aggregated values alongside detail rows.
-- Each sale with department statistics alongside
SELECT employee, department, amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total,
AVG(amount) OVER (PARTITION BY department) AS dept_avg,
COUNT(*) OVER (PARTITION BY department) AS dept_count,
MIN(amount) OVER (PARTITION BY department) AS dept_min,
MAX(amount) OVER (PARTITION BY department) AS dept_max
FROM sales;
-- Percentage of department total
SELECT employee, department, amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1)
AS pct_of_dept
FROM sales;
/* Alice | Engineering | 1200.00 | 13.5%
Alice | Engineering | 1800.00 | 20.2%
Bob | Engineering | 2200.00 | 24.7%
... */
-- Running count and cumulative percentage
SELECT employee, sale_date, amount,
COUNT(*) OVER (ORDER BY sale_date) AS running_count,
SUM(amount) OVER (ORDER BY sale_date) AS running_total,
ROUND(100.0 * SUM(amount) OVER (ORDER BY sale_date)
/ SUM(amount) OVER (), 1) AS cumulative_pct
FROM sales
ORDER BY sale_date;ROWS vs RANGE Frame Specifications
The window frame defines exactly which rows within the partition the function operates on. The full syntax is:
function OVER (
PARTITION BY ...
ORDER BY ...
{ ROWS | RANGE } BETWEEN frame_start AND frame_end
)Frame boundaries can be:
UNBOUNDED PRECEDING— first row of the partitionN PRECEDING— N rows/values before current rowCURRENT ROW— the current rowN FOLLOWING— N rows/values after current rowUNBOUNDED FOLLOWING— last row of the partition
-- ROWS: physical row positions
-- 3-row moving average (current row + 1 before + 1 after)
SELECT sale_date, amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg_3
FROM sales;
-- RANGE: logical value-based boundaries
-- Sum of all sales within 7 calendar days of the current row
SELECT sale_date, amount,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
) AS week_total
FROM sales;
-- Note: RANGE with interval offsets works in PostgreSQL.
-- MySQL and SQLite support RANGE with numeric offsets only.ROWS vs RANGE with duplicate ORDER BY values
-- Bob has two sales with the same amount (2200.00)
-- Watch how ROWS and RANGE differ:
-- ROWS: treats each physical row separately
SELECT employee, amount,
SUM(amount) OVER (ORDER BY amount ROWS UNBOUNDED PRECEDING) AS rows_total
FROM sales WHERE employee = 'Bob';
/* Bob | 2200.00 | 2200.00 (first row only)
Bob | 2200.00 | 4400.00 (both rows) */
-- RANGE: groups peers (same ORDER BY value) together
SELECT employee, amount,
SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_total
FROM sales WHERE employee = 'Bob';
/* Bob | 2200.00 | 4400.00 (both peers included)
Bob | 2200.00 | 4400.00 (same result for peers) */Default frame behavior: When you specify ORDER BY in the OVER clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without ORDER BY, the default is all rows in the partition. Explicitly specifying the frame avoids subtle bugs.
GROUPS Frame (SQL:2011)
The GROUPS frame mode, introduced in the SQL:2011 standard, counts peer groups (sets of rows with equal ORDER BY values) rather than individual rows or value ranges.
-- GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-- Includes the current peer group + 1 peer group before + 1 peer group after
SELECT sale_date, amount,
SUM(amount) OVER (
ORDER BY sale_date
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS groups_sum
FROM sales;
-- Comparison of all three frame modes:
-- Given rows with dates: Jan 3, Jan 5, Jan 7, Jan 7, Jan 8
-- For the first Jan 7 row with "1 PRECEDING AND CURRENT ROW":
-- ROWS: Jan 5 + Jan 7 (one) = 2 rows
-- RANGE: Jan 7 + Jan 7 = 2 rows (value-based: 7-1=6, includes 7s only)
-- GROUPS: Jan 5 + Jan 7 + Jan 7 = 3 rows (1 peer group back + current group)GROUPS is supported in PostgreSQL 11+ and SQLite 3.28+, but not yet in MySQL as of 8.0.
Named Windows (WINDOW Clause)
When multiple window functions share the same OVER specification, you can define a named window with the WINDOW clause to avoid repetition. This also helps the query optimizer reuse the same sort.
-- Without named windows: repetitive OVER clauses
SELECT employee, sale_date, amount,
SUM(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS running_total,
AVG(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS running_avg,
COUNT(*) OVER (PARTITION BY employee ORDER BY sale_date) AS running_count,
LAG(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS prev_amount
FROM sales;
-- With named windows: cleaner and easier to maintain
SELECT employee, sale_date, amount,
SUM(amount) OVER w AS running_total,
AVG(amount) OVER w AS running_avg,
COUNT(*) OVER w AS running_count,
LAG(amount) OVER w AS prev_amount
FROM sales
WINDOW w AS (PARTITION BY employee ORDER BY sale_date);
-- You can define multiple named windows
SELECT employee, department, sale_date, amount,
SUM(amount) OVER emp_window AS emp_running,
SUM(amount) OVER dept_window AS dept_running
FROM sales
WINDOW emp_window AS (PARTITION BY employee ORDER BY sale_date),
dept_window AS (PARTITION BY department ORDER BY sale_date);
-- Named windows can inherit from other named windows
SELECT employee, sale_date, amount,
SUM(amount) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
AVG(amount) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales
WINDOW w AS (PARTITION BY employee ORDER BY sale_date);Common Patterns
Running totals
-- Cumulative revenue over time
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales
ORDER BY sale_date;Moving averages
-- 3-period moving average (current + 2 preceding)
SELECT sale_date, amount,
ROUND(AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3
FROM sales
ORDER BY sale_date;
-- 7-day calendar-based moving average (PostgreSQL)
SELECT sale_date, amount,
ROUND(AVG(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
), 2) AS weekly_avg
FROM sales;Top-N per group
-- Top 2 sales per department
WITH ranked AS (
SELECT employee, department, amount, sale_date,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY amount DESC
) AS rn
FROM sales
)
SELECT department, employee, amount, sale_date
FROM ranked WHERE rn <= 2
ORDER BY department, rn;Gap and island detection
-- Find gaps in sequential IDs
SELECT id,
LEAD(id) OVER (ORDER BY id) AS next_id,
LEAD(id) OVER (ORDER BY id) - id AS gap_size
FROM sales
HAVING gap_size > 1; -- or use a CTE and WHERE
-- Identify consecutive streaks (islands)
-- Employees with sales on consecutive days
SELECT employee, sale_date,
sale_date - CAST(ROW_NUMBER() OVER (
PARTITION BY employee ORDER BY sale_date
) AS INT) AS grp
FROM sales;Percent of total and cumulative distribution
-- Each sale as a percentage of its department total
SELECT employee, department, amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS dept_pct,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS grand_pct
FROM sales
ORDER BY department, amount DESC;
-- Cumulative distribution: what percentage of total revenue each date covers
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS cumulative,
ROUND(100.0 * SUM(amount) OVER (ORDER BY sale_date)
/ SUM(amount) OVER (), 1) AS cumulative_pct
FROM sales
ORDER BY sale_date;Deduplication with ROW_NUMBER
-- Keep only the most recent record per employee
WITH deduped AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY employee
ORDER BY sale_date DESC
) AS rn
FROM sales
)
SELECT id, employee, department, amount, sale_date
FROM deduped WHERE rn = 1;Performance Considerations
Indexes matter
Window functions require sorting rows by the PARTITION BY and ORDER BY columns. The right index eliminates the sort step entirely.
-- For: OVER (PARTITION BY department ORDER BY sale_date)
-- Create a composite index: partition columns first, then order columns
CREATE INDEX idx_sales_dept_date ON sales (department, sale_date);
-- For: OVER (PARTITION BY employee ORDER BY amount DESC)
CREATE INDEX idx_sales_emp_amount ON sales (employee, amount DESC);
-- Covering index (includes all columns the query needs):
CREATE INDEX idx_sales_covering ON sales (department, sale_date, employee, amount);
-- This allows an index-only scan, avoiding the table entirelyMinimize distinct window specifications
-- SLOW: three different sort orders require three separate sorts
SELECT employee, amount, sale_date,
ROW_NUMBER() OVER (ORDER BY amount DESC),
LAG(sale_date) OVER (ORDER BY sale_date),
SUM(amount) OVER (PARTITION BY department ORDER BY sale_date)
FROM sales;
-- FASTER: reuse the same window when possible
SELECT employee, amount, sale_date,
SUM(amount) OVER w AS running,
AVG(amount) OVER w AS running_avg,
LAG(amount) OVER w AS prev
FROM sales
WINDOW w AS (PARTITION BY employee ORDER BY sale_date);Filter before windowing
-- SLOW: window function processes all rows, then WHERE filters
-- (but you CANNOT use WHERE to filter window results directly!)
-- FAST: pre-filter with a CTE, then apply window functions
WITH recent AS (
SELECT * FROM sales
WHERE sale_date >= '2026-01-10'
)
SELECT employee, sale_date, amount,
ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale_date) AS rn
FROM recent;Use EXPLAIN ANALYZE
-- PostgreSQL: check if the window function triggers a sort
EXPLAIN ANALYZE
SELECT employee, amount,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC)
FROM sales;
-- Look for:
-- "Sort" node: means no usable index, data must be sorted in memory
-- "WindowAgg" node: the actual window function execution
-- "Index Scan" feeding directly into WindowAgg: optimalDatabase-Specific Support
-- FEATURE SUPPORT MATRIX
-- +-----------------------+------------+----------+---------+
-- | Feature | PostgreSQL | MySQL 8+ | SQLite |
-- +-----------------------+------------+----------+---------+
-- | ROW_NUMBER/RANK | Yes (8.4+) | Yes | Yes |
-- | LAG/LEAD | Yes (8.4+) | Yes | Yes |
-- | FIRST_VALUE/LAST_VALUE| Yes (8.4+) | Yes | Yes |
-- | NTH_VALUE | Yes (8.4+) | Yes | Yes |
-- | NTILE | Yes (8.4+) | Yes | Yes |
-- | Aggregate OVER | Yes (8.4+) | Yes | Yes |
-- | ROWS frame | Yes | Yes | Yes |
-- | RANGE frame | Yes | Yes | Yes |
-- | RANGE with INTERVAL | Yes | No | No |
-- | GROUPS frame | Yes (11+) | No | Yes |
-- | EXCLUDE clause | Yes (11+) | No | Yes |
-- | WINDOW clause | Yes | Yes | Yes |
-- | FILTER clause | Yes | No | Yes |
-- +-----------------------+------------+----------+---------+
-- PostgreSQL extras: RANGE with intervals, FILTER, GROUPS
SELECT sale_date, amount,
AVG(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) AS weekly_avg
FROM sales;
-- PostgreSQL FILTER clause: conditional window aggregation
SELECT sale_date, department, amount,
SUM(amount) FILTER (WHERE department = 'Engineering')
OVER (ORDER BY sale_date) AS eng_running_total
FROM sales;
-- MySQL: no RANGE with intervals, but ROWS works fine
-- Use a self-join or CTE for date-range windows in MySQL
SELECT s1.sale_date, s1.amount,
AVG(s2.amount) AS weekly_avg
FROM sales s1
JOIN sales s2 ON s2.sale_date BETWEEN s1.sale_date - INTERVAL 7 DAY AND s1.sale_date
GROUP BY s1.id, s1.sale_date, s1.amount;
-- SQLite: supports GROUPS and EXCLUDE but not RANGE with intervals
SELECT sale_date, amount,
SUM(amount) OVER (
ORDER BY sale_date
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS groups_total
FROM sales;Related Resources
- SQL Joins: Complete Guide with Examples — master every JOIN type with visual diagrams
- PostgreSQL: The Complete Guide — in-depth PostgreSQL features including advanced window functions
- SQL Playground — test window function queries interactively in the browser
- SQL Basics Cheat Sheet — quick reference for essential SQL commands
Frequently Asked Questions
What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
ROW_NUMBER assigns a unique sequential integer to every row in the partition with no gaps and no ties. RANK assigns the same number to tied rows but leaves gaps after ties (1, 2, 2, 4). DENSE_RANK also assigns the same number to tied rows but does not leave gaps (1, 2, 2, 3). Use ROW_NUMBER when you need exactly one row per rank, RANK for competition-style ranking, and DENSE_RANK when you need consecutive rank values without gaps.
What is the difference between ROWS and RANGE in a window frame?
ROWS defines the frame based on physical row positions relative to the current row, so ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING always includes exactly 3 rows. RANGE defines the frame based on the logical value of the ORDER BY expression, so RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING includes all rows whose ORDER BY value is within 1 unit of the current row's value. With duplicate ORDER BY values, ROWS treats each row individually while RANGE treats all duplicates as a group. Use ROWS for fixed-size sliding windows and RANGE for value-based windows.
Can I use window functions in a WHERE clause?
No. Window functions are evaluated after WHERE, GROUP BY, and HAVING clauses, so you cannot reference them directly in WHERE. To filter by a window function result, wrap the query in a subquery or CTE. For example, to get only rows where ROW_NUMBER() = 1, write: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1;
Do MySQL and SQLite support window functions?
Yes. MySQL added window function support in version 8.0 (2018) and SQLite added it in version 3.25.0 (2018). PostgreSQL has supported window functions since version 8.4 (2009) and has the most complete implementation. All three databases support the core window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, SUM, AVG, COUNT with OVER) and frame specifications (ROWS, RANGE). The GROUPS frame type and EXCLUDE clause are supported in SQLite and PostgreSQL but not yet in MySQL.
How do I optimize queries that use window functions?
Create indexes that match the PARTITION BY and ORDER BY columns of your window functions, ideally as a composite index with partition columns first and order columns second. Reduce the number of rows before the window function runs by filtering in a CTE or subquery. Avoid using multiple window functions with different PARTITION BY or ORDER BY clauses in the same query, as each requires a separate sort. Use the WINDOW clause to define named windows when multiple functions share the same specification, which helps the optimizer reuse sorts.