SQL Window Functions: OVER Clause, PARTITION BY, ORDER BY, and Frames

SQL Window Functions•Permalink

Core window function syntax, partitions, ordering, and frame clauses for analytic SQL queries.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## OVER clause fundamentals

Count all rows without collapsing them

↗#

SELECT order_id,
       customer_id,
       COUNT(*) OVER () AS total_rows
FROM orders;

# Return total row count on every row.

Aggregate within a partition

↗#

SELECT order_id,
       customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

# Compute a per-customer total on every row.

Assign row numbers inside each partition

↗#

SELECT order_id,
       customer_id,
       created_at,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY created_at DESC
       ) AS rn
FROM orders;

# Number rows in order within each customer.

Reuse a named window

↗#

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
);

# Avoid repeating a long window definition.

Explicit ROWS frame

↗#

SELECT created_at,
       amount,
       SUM(amount) OVER (
         ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;

# Define exactly which physical rows are included.

Use RANGE for peer-aware frames

↗#

SELECT score,
       COUNT(*) OVER (
         ORDER BY score
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_count
FROM exam_results;

# Group rows that share the same ordering value.

Exclude the current row from a frame

↗#

SELECT created_at,
       amount,
       AVG(amount) OVER (
         ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS avg_before_current
FROM orders;

# Compare a row against all earlier rows only.

## Frame clause cheatsheet

Current row only

↗#

ROWS BETWEEN CURRENT ROW AND CURRENT ROW

# Single-row frame.

Last 7 rows

↗#

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

# Rolling seven-row frame.

Current and next 3 rows

↗#

ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING

# Forward-looking frame.

Whole partition

↗#

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

# Every row in the current partition.

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.

Recommended next

No recommendations yet.

Browse all sheets →