sql
SELECT order_id,
customer_id,
COUNT(*) OVER () AS total_rows
FROM orders;Notes
A window aggregate keeps detail rows intact while computing a value across the full result set.
SQL Window Functions: OVER Clause, PARTITION BY, ORDER BY, and Frames
Core window function syntax, partitions, ordering, and frame clauses for analytic SQL queries.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
OVER clause fundamentals
Start with the smallest patterns that make window functions click.
sql
SELECT order_id,
customer_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;Notes
`PARTITION BY` resets the calculation for each logical group.
Assign row numbers inside each partition
Number rows in order within each customer.
sqlANYrow_numberorder by
sql
SELECT order_id,
customer_id,
created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders;Notes
`ORDER BY` inside the window defines sequence for ranking and frame-aware calculations.
sql
SELECT order_id,
amount,
SUM(amount) OVER w AS running_sum,
AVG(amount) OVER w AS running_avg
FROM orders
WINDOW w AS (
PARTITION BY customer_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);Notes
Named windows improve readability when several expressions share the same partition, order, and frame.
sql
SELECT created_at,
amount,
SUM(amount) OVER (
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;Notes
Use `ROWS` when you want frame boundaries to follow row positions rather than peer groups.
sql
SELECT score,
COUNT(*) OVER (
ORDER BY score
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_count
FROM exam_results;Notes
`RANGE` can treat rows with equal sort values as peers, which often changes cumulative results compared with `ROWS`.
Exclude the current row from a frame
Compare a row against all earlier rows only.
sqlANYframehistorical
sql
SELECT created_at,
amount,
AVG(amount) OVER (
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS avg_before_current
FROM orders;Notes
This pattern is useful when you need a historical benchmark without including the current row in the average.
Frame clause cheatsheet
Quick examples of common frame shapes.
sql
ROWS BETWEEN CURRENT ROW AND CURRENT ROWNotes
Useful in examples and to contrast with broader frames.
sql
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWNotes
A classic moving window for row-based smoothing.
sql
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWINGNotes
Handy for queueing, forecast windows, or lead-like summaries.
sql
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGNotes
For some functions, this makes intent explicit and avoids dialect-specific defaults.
More in SQL Window Functions
SQL Window Functions: Performance, EXPLAIN, and Dialect Notes
How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.
SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization
Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.
SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis
Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.
SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics
Positional window functions for first, last, and nth values inside ordered partitions.
SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons
Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.
SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics
Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.