What Is a SQL Window Function (with Examples)?

What Is a SQL Window Function (with Examples)?

A SQL window function performs a calculation across a set of rows that are related to the current row, and it does so without collapsing those rows into a single output row.¹ That single property captures what makes window functions both powerful and confusing: you get the computed result and the original row at the same time.

Window functions became part of the SQL standard in SQL:2003 and are now supported by every major database engine.² PostgreSQL, MySQL 8+, BigQuery, and Snowflake all implement the core syntax with minor dialect differences.

The Mental Model: Window vs. GROUP BY

The easiest way to grasp window functions is to contrast them with GROUP BY using a five-row table.

Suppose you have this table, daily_sales:

id	region	amount
1	East	100
2	East	200
3	West	150
4	West	50
5	East	300

With a plain aggregate plus GROUP BY, you lose the individual rows:

-- Returns 2 rows, not 5
SELECT region, SUM(amount) AS total
FROM daily_sales
GROUP BY region;

With a window function, every row survives:

-- Returns 5 rows, each annotated with the grand total
SELECT id, region, amount,
       SUM(amount) OVER () AS grand_total
FROM daily_sales;

The result still has five rows. The grand_total column on every row equals 600 (the sum of all five rows). Nothing was collapsed.

This is the window: a defined set of rows visible to the function call. The window can span all rows (like above), rows sharing the same region, rows ordered by date, or a sliding frame of the last seven days. You specify the window in the OVER(...) clause.

Syntax Anatomy

The general form is:

window_function(expression)
  OVER (
    PARTITION BY partition_expression
    ORDER BY sort_expression [ASC | DESC]
    frame_clause
  )

window_function(expression)

The function to apply: a ranking function like ROW_NUMBER(), an offset function like LAG(col), or a standard aggregate like SUM(col).

PARTITION BY partition_expression

Divides rows into independent groups (partitions). The function resets and recalculates for each partition. Omitting PARTITION BY makes the entire result set a single partition.

ORDER BY sort_expression

Defines the order of rows within each partition. Required by ranking and offset functions; also controls which rows fall inside the default frame for aggregates.

frame_clause

Pins the exact rows included in the calculation relative to the current row. Expressed as ROWS BETWEEN start AND end or RANGE BETWEEN start AND end. When omitted with an ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Common Window Functions

Ranking Functions

These assign a position number to each row within its partition.

• ROW_NUMBER(): unique sequential integer, no gaps, no ties. Two rows with equal ORDER BY values still get different numbers (the order between them is not guaranteed).
• RANK(): like ROW_NUMBER(), but ties share the same rank and the next rank skips. Returns 1, 1, 3 rather than 1, 1, 2.
• DENSE_RANK(): ties share a rank, but there are no gaps. Returns 1, 1, 2.
• NTILE(n): distributes rows into n roughly equal buckets and returns the bucket number (1 through n).

Offset Functions

These look backward or forward within the ordered partition.

• LAG(col, n, default): returns the value of col from the row n positions before the current row. If no prior row exists, returns default.
• LEAD(col, n, default): the forward equivalent; returns the value from n rows ahead.

Running Aggregates

Standard aggregate functions become window functions when you append OVER(...):

• SUM(col) OVER (...): running total or partition total.
• AVG(col) OVER (...): running or partition average.
• COUNT(*) OVER (...): running or partition count.

Boundary Functions

• FIRST_VALUE(col): returns the first value of col in the window frame.
• LAST_VALUE(col): returns the last value in the frame. With the default frame, LAST_VALUE returns the current row’s own value, not the partition’s last row.

Worked Examples

Example 1: Running Total of Daily Sales per Region

SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales
ORDER BY region, sale_date;

Each row now shows the cumulative sales for its region up to and including that day. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame makes the behavior unambiguous regardless of ties in sale_date.

Example 2: Rank Customers by Lifetime Spend Within Country

SELECT
  customer_id,
  country,
  total_spend,
  RANK() OVER (
    PARTITION BY country
    ORDER BY total_spend DESC
  ) AS spend_rank
FROM customer_summary
ORDER BY country, spend_rank;

The ranking restarts at 1 for every country. Two customers with identical total_spend in the same country share a rank, and the next rank value jumps accordingly.

Example 3: Month-over-Month Delta with LAG

SELECT
  product_id,
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS mom_delta
FROM monthly_revenue
ORDER BY product_id, month;

The 1 in LAG(revenue, 1, 0) means one row back; the 0 is the default value returned for the first month of each product, so the delta is simply the full first-month revenue rather than NULL.

Example 4: 7-Day Rolling Average

SELECT
  event_date,
  daily_count,
  AVG(daily_count) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM page_views
ORDER BY event_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row plus the six rows immediately before it, forming a true 7-day sliding window. Using ROWS rather than RANGE ensures you get exactly 7 rows, not all rows matching the same date value.

Example 5: Bucket into Deciles by Revenue

SELECT
  customer_id,
  revenue,
  NTILE(10) OVER (
    ORDER BY revenue DESC
  ) AS revenue_decile
FROM customer_revenue
ORDER BY revenue_decile, revenue DESC;

NTILE(10) divides all customers into 10 equal buckets (decile 1 = top 10% by revenue). If the row count isn’t evenly divisible by 10, the earlier buckets get one extra row each.

Frame Clauses: The Part Most People Skip

Frame clauses define which rows within a partition are included in the calculation relative to the current row. There are three frame modes:

ROWS

Counts physical rows. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always includes exactly 3 rows (or fewer at the partition boundary), regardless of duplicate values.

RANGE

Includes all rows whose ORDER BY value falls within the specified logical offset of the current row’s value. With ORDER BY date, RANGE BETWEEN INTERVAL ‘7 days’ PRECEDING AND CURRENT ROW includes all rows within 7 calendar days, not just the 7 nearest rows.

GROUPS

Similar to RANGE, but counts groups of equal ORDER BY values rather than individual rows or logical offsets. Available in PostgreSQL 11+ and some other engines.

The default frame gotcha. When you write ORDER BY col inside OVER(...) without specifying a frame, the database applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This is almost always correct for a running total. However, if your ORDER BY column has ties, “CURRENT ROW” in RANGE mode means all rows sharing the same ORDER BY value. A running sum at a tie point jumps to include all tied rows at once, which surprises most people. Switch to ROWS mode to get strict physical-row behavior.

Frame boundary keywords:

• UNBOUNDED PRECEDING: the first row of the partition.
• n PRECEDING: n rows (or range units) before the current row.
• CURRENT ROW: the current row itself.
• n FOLLOWING: n rows after the current row.
• UNBOUNDED FOLLOWING: the last row of the partition.

Common Pitfalls

1. Forgetting PARTITION BY when you wanted per-group calculations. SUM(amount) OVER (ORDER BY date) gives you a running total across the entire table. If you wanted it per region, you need SUM(amount) OVER (PARTITION BY region ORDER BY date). The omission is silent: no error, just wrong numbers.

2. RANK vs. ROW_NUMBER confusion. Use ROW_NUMBER() when you need a unique identifier for each row and ties should be broken arbitrarily. Use RANK() when you want tied rows to share a position and you don’t mind gaps. Use DENSE_RANK() when you want ties to share a position without gaps.

3. Window functions cannot appear in WHERE clauses. This query fails:

-- ERROR: window functions are not allowed in WHERE
SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM results
WHERE ROW_NUMBER() OVER (ORDER BY score DESC) <= 10;

Wrap the window function in a subquery or CTE:

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
  FROM results
)
SELECT id FROM ranked WHERE rn <= 10;

This is by design: WHERE filters rows before window functions are evaluated. The filtering stage has no window-function values yet.³

4. LAST_VALUE and the frame boundary. LAST_VALUE(col) OVER (PARTITION BY x ORDER BY y) with the default frame returns the current row’s own value, not the last row in the partition. To get the true last value, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

FAQ

Do window functions work in MySQL? Yes, from MySQL 8.0 onward. MySQL 5.x does not support them. The syntax is identical to the SQL:2003 standard, so queries written for PostgreSQL or Snowflake typically run on MySQL 8 without changes.¹

Can I use multiple window functions in the same SELECT? Yes. Each window function has its own independent OVER(...) clause. If multiple functions share the same window definition, you can name it with a WINDOW clause at the bottom of the query to avoid repetition:

SELECT
  region,
  amount,
  SUM(amount) OVER w AS running_total,
  AVG(amount) OVER w AS running_avg
FROM daily_sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date);

How do window functions affect query performance? Window functions typically require a sort pass over the data, which is O(n log n) in the general case. Most databases optimize repeated window operations with the same partition and order into a single sort. For large tables, ensure the PARTITION BY columns are indexed to reduce the scan cost; the frame computation itself is usually not the bottleneck.

Is there a difference between window functions in Postgres, BigQuery, and Snowflake? The core OVER / PARTITION BY / ORDER BY syntax is standard. Differences appear at the edges: BigQuery does not support the GROUPS frame mode; Snowflake has additional analytic functions beyond the SQL:2003 set; PostgreSQL 11+ introduced GROUPS mode and range_offset expressions for RANGE frames.³ For portable SQL, stick to ROWS frames and the core ranking and offset functions.

When should I use a window function instead of a self-join? Almost always prefer the window function. A self-join to compute running totals or row-level comparisons is O(n²) and grows poorly. Window functions give you the same result in a single pass.

Related reading

• How to Choose a Data Analytics Certificate in 2026 — most analyst certificate curricula relegate window functions to one optional week; this is where they re-emerge in production work.
• How AI-Assisted Analytics Workflows Actually Work in 2026 — BI copilots that generate SQL still routinely produce window-function expressions analysts have to verify by hand.
• How to Read Research Papers as a Working Engineer — the SQL:2003 standard PDF reads like a research paper; the three-pass method works on standards documents too.

---

StackTower AI editorial team: SQL fundamentals, database engine documentation, and data engineering explainers. This article was written with AI assistance and reviewed by the StackTower AI editorial board.

Footnotes

1. PostgreSQL Documentation, “Window Functions,” https://www.postgresql.org/docs/current/tutorial-window.html. Primary vendor reference covering OVER, PARTITION BY, ORDER BY, and frame clauses. ↩ ↩²

2. Window functions were standardized in SQL:2003 (ISO/IEC 9075-2:2003). See also Wikipedia, “SQL window function,” https://en.wikipedia.org/wiki/Window_function_(SQL) for standardization history and engine adoption timeline. ↩

3. Snowflake Documentation, “Window Functions,” https://docs.snowflake.com/en/sql-reference/functions-analytic. Covers Snowflake-specific analytic functions, the QUALIFY clause, and frame-mode support differences. ↩ ↩²