You've been building rolling averages and running totals with window functions for a while now, and you've settled into a comfortable pattern: OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). It works. Your dashboards look great. Then one day a business analyst flags something strange — your 7-day rolling revenue figures don't match what the finance team is computing in Excel, and the discrepancy only shows up on days where multiple transactions share the same timestamp. You dig in, stare at your query, and realize you've been reaching for ROWS out of habit without truly understanding when RANGE or GROUPS would be the right tool.
This is the moment where most SQL practitioners discover that window frame specification is not a single concept — it's three distinct behaviors that differ in fundamental ways, each with legitimate use cases, each with surprising edge cases. The difference between ROWS, RANGE, and GROUPS isn't syntactic sugar. It's the difference between calculating a rolling metric based on physical row position versus logical peer groups, and getting that distinction wrong can corrupt analysis in ways that are extremely difficult to audit after the fact.
By the end of this lesson, you'll have genuine competence in all three frame modes. You'll know not just the syntax but the mental model behind each one, when each is appropriate, how ties and duplicates affect results, and how to diagnose and fix frame-related bugs in production queries. You'll also understand the performance implications of each mode, which matters significantly at scale.
What you'll learn:
ROWS, RANGE, and GROUPS frame modes and the internal mechanics driving eachRANGE and GROUPS calculationsUNBOUNDED PRECEDING, CURRENT ROW, N PRECEDING/FOLLOWING, and the nuances of eachThis is an expert-level lesson. You should already be comfortable with:
PARTITION BY, ORDER BY, ROW_NUMBER(), RANK(), DENSE_RANK()ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWSUM(), AVG(), COUNT() used with OVER()The examples in this lesson use PostgreSQL syntax (version 11+), which has the most complete implementation of the SQL standard for window frames. Behavioral differences in MySQL, SQL Server, and BigQuery are noted where they matter.
Before we can contrast the three frame modes, we need a shared mental model of what a window frame actually is. When a window function evaluates, it doesn't just see the entire partition — it sees a subset of the partition defined by the frame. The frame moves row by row through the partition, and its boundaries are expressed relative to the current row.
Here's the full grammar of a frame clause:
{ ROWS | RANGE | GROUPS }
BETWEEN frame_start AND frame_end
-- Where frame_start and frame_end are:
UNBOUNDED PRECEDING
N PRECEDING
CURRENT ROW
N FOLLOWING
UNBOUNDED FOLLOWING
The frame mode — ROWS, RANGE, or GROUPS — determines what "N PRECEDING" and "CURRENT ROW" mean. This is the crux of everything that follows.
Let's build a dataset we'll use throughout this lesson. Imagine you're an analyst at a fintech company tracking daily transaction volume across merchant categories:
CREATE TABLE daily_transactions (
transaction_date DATE,
merchant_category TEXT,
total_amount NUMERIC(12, 2),
transaction_count INTEGER
);
INSERT INTO daily_transactions VALUES
('2024-01-01', 'retail', 15420.50, 142),
('2024-01-02', 'retail', 18930.00, 187),
('2024-01-02', 'retail', 22100.75, 201), -- duplicate date, same category
('2024-01-03', 'retail', 9875.25, 94),
('2024-01-04', 'retail', 31240.00, 298),
('2024-01-04', 'retail', 28950.50, 275), -- another duplicate
('2024-01-05', 'retail', 19650.00, 188),
('2024-01-06', 'retail', 24310.25, 231),
('2024-01-07', 'retail', 17890.75, 170),
('2024-01-08', 'retail', 21445.00, 204);
Notice the deliberate duplicate dates. Real-world transaction data frequently has this shape — multiple source systems, multiple batch loads, or genuinely multiple records for the same logical period. This is exactly what will expose the behavioral differences between our three frame modes.
ROWS is the simplest frame mode to reason about because it's purely mechanical. When you specify ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, the database literally counts backwards two rows in the sorted partition and includes everything from that physical row to the current row. It doesn't care about the values in the ORDER BY column. It counts rows.
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date, total_amount
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3row_sum
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
| transaction_date | total_amount | rolling_3row_sum |
|---|---|---|
| 2024-01-01 | 15420.50 | 15420.50 |
| 2024-01-02 | 18930.00 | 34350.50 |
| 2024-01-02 | 22100.75 | 56451.25 |
| 2024-01-03 | 9875.25 | 50906.00 |
| 2024-01-04 | 28950.50 | 60926.50 |
| 2024-01-04 | 31240.00 | 70065.75 |
| 2024-01-05 | 19650.00 | 79840.50 |
| 2024-01-06 | 24310.25 | 75200.25 |
| 2024-01-07 | 17890.75 | 61851.00 |
| 2024-01-08 | 21445.00 | 63646.00 |
The frame for row 4 (Jan 3, $9,875.25) includes rows 2, 3, and 4 — the previous two rows and the current row, regardless of what dates they carry. The date 2024-01-02 appears twice, and each occurrence is treated as a completely independent row.
ROWS is appropriate when you have a genuinely row-based dataset where each row represents exactly one distinct observation and the physical ordering is meaningful. Think of these scenarios:
ROWS gives you predictable, deterministic behavior as long as your ORDER BY produces a total ordering (no ties). The moment you have ties in your ORDER BY and you use ROWS, your frame boundaries become arbitrary — which rows end up in the frame depends on the sort algorithm's behavior with equal keys, which is generally unspecified.
Warning: Using
ROWSwith a non-uniqueORDER BYis a common source of subtle, hard-to-reproduce bugs. If two rows have the same ORDER BY values, the database is free to place them in either order, meaning the same query can return different results on different runs or after a table reorganization. Always ensure your ORDER BY is deterministic when usingROWS.
In ROWS mode, the N in N PRECEDING and N FOLLOWING is always an integer representing a literal row count. It has no relationship to the values in the ORDER BY column. ROWS BETWEEN 7 PRECEDING AND CURRENT ROW means "the 7 rows before this one and the current row" — 8 rows total, regardless of whether those rows span 7 days or 7 months.
RANGE is where things get philosophically interesting. Instead of counting physical rows, RANGE defines the frame boundary based on the values in the ORDER BY column. The frame includes all rows whose ORDER BY value falls within a specified range of the current row's ORDER BY value.
This introduces the concept of peer rows: two rows are peers if they have identical ORDER BY values. In RANGE mode, CURRENT ROW means "the current row and all its peers."
Let's see this directly:
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_range,
SUM(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_rows
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
| transaction_date | total_amount | running_total_range | running_total_rows |
|---|---|---|---|
| 2024-01-01 | 15420.50 | 15420.50 | 15420.50 |
| 2024-01-02 | 18930.00 | 56451.25 | 34350.50 |
| 2024-01-02 | 22100.75 | 56451.25 | 56451.25 |
| 2024-01-03 | 9875.25 | 66326.50 | 66326.50 |
| 2024-01-04 | 28950.50 | 126517.00 | 95276.50 |
| 2024-01-04 | 31240.00 | 126517.00 | 126517.00 |
| 2024-01-05 | 19650.00 | 146167.00 | 146167.00 |
Look at what happens on January 2nd. Both rows with that date get the same running total in RANGE mode: 56,451.25. That's because RANGE with CURRENT ROW as the end boundary includes all peers of the current row. Both Jan 2 rows are peers (same ORDER BY value), so both of them see the full Jan 2 contribution.
This is not a bug. This is the correct semantic when you're asking "what's the running total as of this date?" Both rows on January 2nd should report the same cumulative figure because they represent the same logical point in time.
The real power of RANGE mode emerges when you combine it with explicit numeric offsets. But there's a critical constraint: when using N PRECEDING or N FOLLOWING in RANGE mode, your ORDER BY must be a single column, and that column must be numeric or a date/interval type that supports arithmetic.
-- Rolling 7-day sum using RANGE with date arithmetic
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7day_sum
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
This is conceptually different from ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. The RANGE version says "include all rows where transaction_date is within 6 days before the current row's date, inclusive." If you have 3 rows on January 4th, the rolling 7-day sum for each of those rows will include all rows from January 4th back through January 29th (December 29th in context), including all records on those dates.
The ROWS version would just take the 6 physically preceding rows and the current row — 7 rows total, regardless of dates.
-- Contrasting ROWS vs RANGE for a "last 7 calendar days" calculation
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rows_7_prior_rows, -- last 7 rows, not necessarily 7 days
SUM(total_amount) OVER (
ORDER BY transaction_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS range_7_prior_days -- last 7 calendar days of data
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
Key insight: Use
RANGE BETWEEN INTERVAL 'N days' PRECEDING AND CURRENT ROWwhen you want to capture a true calendar window regardless of how many rows fall in that window. UseROWS BETWEEN N PRECEDING AND CURRENT ROWwhen you want exactly N+1 observations regardless of their timestamps.
Here's something that trips up even experienced practitioners. When you write OVER (ORDER BY some_column) without any explicit frame clause, SQL does not default to the entire partition. The default frame is:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This means your window function, by default, uses RANGE semantics. For a running sum with no duplicate ORDER BY values, this matches what you'd get from ROWS. But if you have ties, the default RANGE behavior will cause all tied rows to show the same aggregate — which may or may not be what you intended.
-- These look the same but behave differently on tied dates:
SELECT SUM(total_amount) OVER (ORDER BY transaction_date) AS implicit_default
-- Is equivalent to:
SELECT SUM(total_amount) OVER (ORDER BY transaction_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS explicit_default
-- NOT equivalent to:
SELECT SUM(total_amount) OVER (ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_version
Always be explicit about your frame clause when correctness matters.
GROUPS is the newest of the three frame modes, introduced in the SQL:2011 standard and implemented in PostgreSQL 11+. It's also the least understood and the most underused. GROUPS mode is a synthesis of ROWS and RANGE: it counts peer groups rather than individual rows or value ranges.
Two rows belong to the same peer group if they have identical ORDER BY values — the same definition as RANGE mode. The difference is in how the frame boundary offset is interpreted. In GROUPS mode, N PRECEDING means "go back N peer groups from the current row's peer group."
Let's make this concrete:
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_2day_sum,
SUM(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_2row_sum,
SUM(total_amount) OVER (
ORDER BY transaction_date
RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW
) AS range_2day_sum
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
For the January 4th rows:
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW: includes all Jan 4 rows (current group) + all Jan 3 rows (1 group back). Result: all rows from both dates.ROWS BETWEEN 1 PRECEDING AND CURRENT ROW: includes just 2 physical rows — the current row and the one immediately before it.RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW: includes all rows where the date is within 1 day of Jan 4 (Jan 3 and Jan 4). Same result as GROUPS in this case, but only because dates happen to be consecutive integers.The key distinction for GROUPS: when you say 1 PRECEDING, you always get exactly one full peer group back, whatever size that group is. If Jan 4 has 50 rows and Jan 3 has 1 row, GROUPS 1 PRECEDING includes all 51 rows.
-- Practical use: rolling 3-date-period analysis that handles multiple rows per date
SELECT
transaction_date,
merchant_category,
total_amount,
SUM(total_amount) OVER (
PARTITION BY merchant_category
ORDER BY transaction_date
GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3period_sum,
COUNT(*) OVER (
PARTITION BY merchant_category
ORDER BY transaction_date
GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rows_in_frame
FROM daily_transactions
ORDER BY merchant_category, transaction_date, total_amount;
The rows_in_frame column reveals what's actually happening — the frame includes variable numbers of physical rows depending on how many records exist per date.
GROUPS is the correct mode when your data has a natural grouping concept that doesn't map to a fixed numeric interval, and you want to include a specific number of periods rather than rows or value ranges.
Financial analysis provides the clearest examples:
-- Portfolio performance: rolling 4-quarter average
-- where each quarter may have multiple fund entries
SELECT
fiscal_quarter,
fund_name,
quarterly_return,
AVG(quarterly_return) OVER (
PARTITION BY fund_name
ORDER BY fiscal_quarter
GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS rolling_4q_avg_return
FROM fund_quarterly_returns
ORDER BY fund_name, fiscal_quarter;
This correctly computes a 4-quarter rolling average regardless of how many funds or share classes appear per quarter. Using ROWS BETWEEN 3 PRECEDING AND CURRENT ROW would give you 4 arbitrary rows, not 4 quarters.
Database support note:
GROUPSis not universally supported. PostgreSQL 11+ supports it fully. SQL Server does not supportGROUPSas of SQL Server 2022. MySQL 8.0 does not supportGROUPS. BigQuery does not supportGROUPS. If you're writing cross-platform SQL, this is a significant portability constraint.
Now that we understand the three frame modes, let's look carefully at the boundary expressions and some subtle behaviors that matter in production.
These are straightforward: include everything from the start of the partition to the current frame position, or from the current position to the end. They work the same way regardless of frame mode.
-- Running total from start of partition
SUM(revenue) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- Inverse: remaining total from current row to end of partition
SUM(revenue) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
In ROWS mode, CURRENT ROW means exactly the current physical row.
In RANGE and GROUPS mode, CURRENT ROW at the end of the frame means "all rows that are peers of the current row" (i.e., the entire peer group of the current row). At the start of the frame, CURRENT ROW means "the first row of the current peer group."
This asymmetry matters:
-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:
-- Includes current peer group through end of partition
-- NOT just from the current physical row to the end
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS remaining_total
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date, total_amount;
For both January 2nd rows, remaining_total will show the same value — the sum from January 2nd (including both Jan 2 rows) through the end. They're peers, so they see the same frame end.
The SQL standard also supports EXCLUDE clauses for fine-grained control over what's included in the frame:
-- Standard SQL EXCLUDE syntax (PostgreSQL 14+)
SUM(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
EXCLUDE CURRENT ROW -- Exclude just the current row
-- EXCLUDE GROUP -- Exclude current row and its peers
-- EXCLUDE TIES -- Exclude peers but not current row
-- EXCLUDE NO OTHERS -- Default, exclude nothing
)
EXCLUDE is particularly useful when computing influence statistics — for example, a "leave-one-out" rolling average where you want the average of surrounding rows excluding the current observation.
NULL values in the ORDER BY column create a special case. In PostgreSQL, NULLs sort last by default. Two NULL values are considered peers in RANGE mode (both are NULL, so they're "equal"). In RANGE BETWEEN N PRECEDING AND CURRENT ROW, a row with a NULL ORDER BY value will only include other NULL rows in its "current row" frame end (since NULL ± N is still NULL, and no non-NULL value equals NULL).
-- Be explicit about NULL ordering when your data has gaps
SELECT
transaction_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY transaction_date NULLS FIRST
RANGE BETWEEN INTERVAL '3 days' PRECEDING AND CURRENT ROW
) AS rolling_sum_null_aware
FROM daily_transactions;
Let's work through several realistic analytical scenarios and determine the correct frame specification for each.
Scenario: A CFO wants a 30-day rolling revenue total. The data has some days with multiple records and some days with no records (weekends, holidays).
WITH daily_revenue AS (
SELECT
transaction_date,
merchant_category,
SUM(total_amount) AS daily_total
FROM daily_transactions
GROUP BY transaction_date, merchant_category
)
SELECT
transaction_date,
merchant_category,
daily_total,
SUM(daily_total) OVER (
PARTITION BY merchant_category
ORDER BY transaction_date
RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
) AS rolling_30d_revenue
FROM daily_revenue
ORDER BY merchant_category, transaction_date;
Note the INTERVAL '29 days' — we want a 30-day window inclusive of the current day, so we go back 29 days. Pre-aggregating to daily totals before windowing eliminates the peer group complexity for this use case.
Scenario: A trading algorithm needs a 20-observation moving average of price signals, where each row is one trade execution. The physical count of observations matters, not the time span.
SELECT
execution_id,
execution_timestamp,
price_signal,
AVG(price_signal) OVER (
PARTITION BY instrument_id
ORDER BY execution_id -- execution_id is unique and sequential
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
) AS ma_20,
STDDEV(price_signal) OVER (
PARTITION BY instrument_id
ORDER BY execution_id
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
) AS stddev_20
FROM trade_executions
ORDER BY instrument_id, execution_id;
Using execution_id as the ORDER BY (assuming it's unique and sequential) gives us a total ordering with no ties, making ROWS fully deterministic.
Scenario: An analyst wants a trailing 4-quarter average of NPS scores, where multiple survey responses are collected per quarter per region.
SELECT
survey_quarter,
region,
respondent_id,
nps_score,
AVG(nps_score) OVER (
PARTITION BY region
ORDER BY survey_quarter
GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS trailing_4q_avg_nps,
COUNT(*) OVER (
PARTITION BY region
ORDER BY survey_quarter
GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS response_count_in_window
FROM nps_survey_responses
ORDER BY region, survey_quarter, respondent_id;
GROUPS is ideal here because each survey period (quarter) is a natural unit, and we want exactly 4 periods of history regardless of response volume per quarter.
Sometimes you want to look at a range around the current row symmetrically. This is useful for centering moving averages:
-- Centered 7-day moving average (3 days before, current, 3 days after)
SELECT
transaction_date,
total_amount,
AVG(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centered_7day_avg,
-- For comparison, trailing average
AVG(total_amount) OVER (
ORDER BY transaction_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS trailing_7day_avg
FROM daily_transactions
WHERE merchant_category = 'retail'
ORDER BY transaction_date;
Warning: Centered moving averages using
ROWS ... FOLLOWINGintroduce a look-ahead bias if used in any predictive or operational context. They're legitimate for retrospective smoothing (e.g., trend visualization) but inappropriate for features in real-time models.
Window frame calculations can be computationally expensive, and the frame mode you choose directly affects performance. Let's examine the mechanics.
Every windowed calculation requires the data to be sorted by the PARTITION BY and ORDER BY columns. This is unavoidable. The sort cost is O(n log n) in the number of rows in the table (or partition). What varies by frame mode is the cost of computing the aggregate for each row's frame.
For ROWS mode with UNBOUNDED PRECEDING AND CURRENT ROW, most databases implement a streaming or incremental aggregation strategy. As the window moves forward one row, the aggregate is updated by adding the new value (and optionally subtracting the one that falls out of the frame). For a SUM, this is O(1) per row — very efficient.
For ROWS BETWEEN N PRECEDING AND CURRENT ROW with a specific N, databases typically use a sliding window approach with O(1) amortized cost per row for associative operations like SUM and COUNT, but O(N) for non-invertible operations like MAX, MIN, and MEDIAN that can't be "un-aggregated" as the trailing edge moves.
-- This is efficient (associative, invertible):
SUM(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
-- This is potentially expensive (non-invertible):
MAX(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
For MAX with a sliding window, when the current maximum falls out of the frame, the database has to rescan the frame to find the new maximum. Some databases (PostgreSQL in particular) use segment trees or deque-based algorithms to handle this in O(log N) per row, but it's still more expensive than a simple running sum.
RANGE mode with UNBOUNDED PRECEDING AND CURRENT ROW is generally as efficient as ROWS for the same operation, because the database can recognize that peer groups must be processed together and can use a similar streaming strategy.
RANGE mode with explicit interval offsets (RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) requires the database to binary-search for the frame start for each row, resulting in O(log n) per row for the boundary location, plus the aggregation cost. This is more expensive than ROWS BETWEEN N PRECEDING for large partitions.
GROUPS mode typically performs similarly to RANGE in terms of implementation strategy — the database identifies peer group boundaries and computes accordingly. The cost per row depends on the size of peer groups and the nature of the aggregate.
The single biggest performance lever for window functions is providing the optimizer with a pre-sorted index.
-- For queries partitioned by merchant_category and ordered by transaction_date:
CREATE INDEX idx_transactions_category_date
ON daily_transactions (merchant_category, transaction_date);
-- For RANGE with date interval offsets, include the amount if it's commonly aggregated:
CREATE INDEX idx_transactions_category_date_amount
ON daily_transactions (merchant_category, transaction_date)
INCLUDE (total_amount);
When an index covers the PARTITION BY and ORDER BY columns in that order, the database can use an index scan directly for the sort phase, eliminating the explicit sort node from the execution plan. This can be a dramatic speedup for large tables.
-- Check if your query is using the index efficiently:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
transaction_date,
merchant_category,
SUM(total_amount) OVER (
PARTITION BY merchant_category
ORDER BY transaction_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_sum
FROM daily_transactions;
Look for Index Scan (or Index Only Scan) in the plan rather than Sort. The presence of an explicit Sort node indicates the optimizer couldn't use an index for ordering and is paying the sort cost on each execution.
When you need several window functions with the same partition and order, SQL can often reuse a single sort pass:
-- These share a single sort because the window definition is identical:
SELECT
transaction_date,
merchant_category,
total_amount,
SUM(total_amount) OVER w AS rolling_sum,
AVG(total_amount) OVER w AS rolling_avg,
COUNT(*) OVER w AS rolling_count,
MAX(total_amount) OVER w AS rolling_max
FROM daily_transactions
WINDOW w AS (
PARTITION BY merchant_category
ORDER BY transaction_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY merchant_category, transaction_date;
The WINDOW clause (a named window definition) not only makes the query readable but signals to the optimizer that these functions share a window, enabling sort reuse. If you inline the frame specification differently for each function — even if the specifications are logically equivalent — some optimizers will sort multiple times.
Let's bring everything together with a realistic analytical challenge. You're working with an e-commerce platform and need to build a sales performance dashboard. Here's your dataset:
CREATE TABLE ecommerce_sales (
order_id BIGINT,
order_date DATE,
customer_segment TEXT,
revenue NUMERIC(10, 2),
units_sold INTEGER,
return_flag BOOLEAN DEFAULT FALSE
);
-- Insert sample data (a subset shown for brevity)
INSERT INTO ecommerce_sales VALUES
(1001, '2024-01-15', 'enterprise', 12500.00, 50, FALSE),
(1002, '2024-01-15', 'enterprise', 8900.00, 36, FALSE),
(1003, '2024-01-16', 'enterprise', 15200.00, 61, FALSE),
(1004, '2024-01-17', 'enterprise', 11800.00, 47, TRUE),
(1005, '2024-01-17', 'enterprise', 22100.00, 88, FALSE),
(1006, '2024-01-18', 'enterprise', 9650.00, 39, FALSE),
(1007, '2024-01-19', 'enterprise', 18300.00, 73, FALSE),
(1008, '2024-01-20', 'enterprise', 14750.00, 59, FALSE),
(1009, '2024-01-21', 'enterprise', 21000.00, 84, FALSE),
(1010, '2024-01-21', 'enterprise', 17500.00, 70, TRUE),
(1011, '2024-01-22', 'enterprise', 19800.00, 79, FALSE),
(1012, '2024-01-23', 'enterprise', 13200.00, 53, FALSE);
Exercise Tasks:
Task 1: Compute a true 7-calendar-day rolling revenue total for each customer segment. This should include all orders from any day within the 7-day window, including multiple orders on the same day. Use the appropriate frame mode.
-- Your solution here. Consider:
-- What frame mode handles "7 calendar days" correctly?
-- How do you handle multiple orders per day?
-- What's the correct interval expression for a 7-day window inclusive of today?
SELECT
order_date,
customer_segment,
revenue,
SUM(revenue) OVER (
PARTITION BY customer_segment
ORDER BY order_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7d_revenue
FROM ecommerce_sales
ORDER BY customer_segment, order_date, order_id;
Task 2: For each order, compute a "per-order" rolling average that counts exactly the 5 most recent orders (including the current one), regardless of dates. Also include a count of how many orders are in the rolling window (to detect when the window hasn't filled yet at the beginning of the dataset).
SELECT
order_id,
order_date,
customer_segment,
revenue,
ROUND(AVG(revenue) OVER (
PARTITION BY customer_segment
ORDER BY order_id -- use order_id for deterministic ordering
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
), 2) AS rolling_5order_avg,
COUNT(*) OVER (
PARTITION BY customer_segment
ORDER BY order_id
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS orders_in_window
FROM ecommerce_sales
ORDER BY customer_segment, order_id;
Task 3 (Challenge): Compute a running total of revenue that correctly handles the return flag. For returned orders, their revenue should be subtracted. But the running total for all orders on the same date should show the same final value (as if all same-day orders were processed atomically).
-- Hint: Use RANGE with UNBOUNDED PRECEDING to get same-date peers showing the same total
SELECT
order_id,
order_date,
customer_segment,
revenue,
return_flag,
SUM(CASE WHEN return_flag THEN -revenue ELSE revenue END) OVER (
PARTITION BY customer_segment
ORDER BY order_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS net_running_total
FROM ecommerce_sales
ORDER BY customer_segment, order_date, order_id;
Observe that all orders on the same date show the same net_running_total — the full net of that date's activity. This is typically the correct financial reporting behavior.
Symptom: Your rolling 7-day average produces incorrect values when some days have multiple rows and other days have none.
Diagnosis: You're using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which counts 7 physical rows, not 7 days.
Fix: Switch to RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW. If you have multiple rows per day and want the window to be row-based but calendar-bounded, pre-aggregate to daily totals first, then apply RANGE.
Symptom: Your query returns different results on different runs, or results change after a VACUUM, table rewrite, or index rebuild.
Diagnosis: Your ORDER BY in the window function has ties, and you're using ROWS mode. The sort order of tied rows is undefined.
Fix: Add a unique column (like a primary key) as a tiebreaker in the ORDER BY:
-- Fragile:
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW -- with ORDER BY transaction_date
-- Correct:
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW -- with ORDER BY transaction_date, order_id
Symptom: A window function without an explicit frame clause produces unexpected results with tied ORDER BY values.
Diagnosis: The implicit default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. All rows that are tied with the current row are included in the frame end.
Fix: Always specify your frame explicitly. Never rely on the default.
Symptom: Your query fails with an error like "RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column."
Diagnosis: You attempted to use RANGE BETWEEN N PRECEDING AND CURRENT ROW with a multi-column ORDER BY. This is not supported — the offset in RANGE mode applies to the ORDER BY expression, which must be a single scalar value.
Fix: If you need a multi-column ORDER BY for determinism but want RANGE semantics, consider restructuring: use ROWS with a fully unique ORDER BY, or pre-aggregate to ensure one row per period.
Symptom: A developer adds GROUPS to a window function and expects it to collapse rows the way GROUP BY does.
Diagnosis: GROUPS in a window frame clause is a frame mode, not an aggregation directive. It does not reduce rows — it controls which peer groups are included in the frame. All original rows remain in the output.
Fix: Understand that GROUPS changes how the frame boundaries are counted — in units of peer groups rather than rows or value distances. Rows are not collapsed.
Symptom: A data scientist builds a rolling feature using ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING and gets suspiciously high model performance.
Diagnosis: Using FOLLOWING in a rolling feature includes future data for each row, introducing look-ahead bias. In a time-series model, this means each training example has access to information that wouldn't have been available at prediction time.
Fix: For any operational or predictive use case, restrict your frame to UNBOUNDED PRECEDING AND CURRENT ROW or N PRECEDING AND CURRENT ROW. Use FOLLOWING only for retrospective analysis and visualization.
Let's crystallize what we've covered into durable mental models.
ROWS counts physical rows. Use it when each row is a distinct, meaningful observation and your ORDER BY is unique (or you've made it unique with a tiebreaker). It's the most predictable mode and the safest default for row-based sliding windows.
RANGE uses value-based boundaries. The frame includes all rows whose ORDER BY value falls within a distance from the current row's ORDER BY value. Use it when you want a true calendar or numeric interval window (e.g., "all data from the last 30 days"), or when you want tied rows to see identical aggregate values (correct financial running totals). The ORDER BY must be a single column when using explicit offset values.
GROUPS counts peer groups. The frame includes all rows belonging to a specific number of peer groups before and after the current row's group. Use it when your logical unit of analysis is a period or category that may span multiple rows, and you want to count N such periods. Less portable than ROWS and RANGE.
The default frame when you omit the frame clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — always be explicit.
Performance hierarchy for rolling aggregates (fastest to slowest):
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — streaming aggregationROWS BETWEEN N PRECEDING AND CURRENT ROW with invertible aggregates — sliding windowRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — peer-group-aware streamingRANGE BETWEEN INTERVAL N PRECEDING AND CURRENT ROW — binary search per rowROWS BETWEEN N PRECEDING AND CURRENT ROW with non-invertible aggregates (MAX, MIN) — rescan on trailing edgeYour next steps:
EXCLUDE clause (PostgreSQL 14+, SQL Server 2022+) for leave-one-out calculations and peer exclusion.FILTER clauses with window functions — a powerful complement to frame specifications for conditional rolling aggregates: SUM(revenue) FILTER (WHERE return_flag = FALSE) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW).Frame specification is one of those areas where genuine mastery makes you a significantly more reliable analyst. The bugs it prevents are the hardest kind — numerically plausible, business-impacting, and easy to miss in review. Now you know exactly what you're specifying and why.
Learning Path: Advanced SQL Queries