SQL window functions: the analytics queries that actually matter

A window function performs a calculation across a set of rows related to the current row, without collapsing those rows into one. That single property is why a window function can produce a running total, rank customers by revenue, or compare this month to last month in one query while still returning every original row. If you have ever run a query, exported it to a spreadsheet, and added a helper column for a running sum or a rank, a window function does that work directly in SQL.

This guide is for analysts, operators, and product managers who already write basic SQL and want the specific patterns that answer real business questions. It covers what a window function is, the syntax, the functions worth knowing, worked examples for common questions, and the mistakes that quietly produce wrong numbers.

What is a window function, and how is it different from GROUP BY?

GROUP BY collapses rows. If you group orders by customer and sum the amount, you get one row per customer and lose the individual orders. A window function keeps every row and attaches a computed value alongside it.

Compare the two on the same question, “total revenue per customer”:

-- GROUP BY: one row per customer, orders are gone
SELECT customer_id, SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id;

-- Window function: every order row, with the customer total attached
SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

The window version is what you want when you need the detail and the aggregate together: each order next to that customer’s lifetime spend, each day’s revenue next to the running total, each row next to its rank.

The anatomy of a window function

Every window function follows the same shape:

function() OVER (PARTITION BY ... ORDER BY ... frame)

• OVER marks the calculation as a window function. The parentheses define the window: the set of rows the function can see.
• PARTITION BY splits rows into independent groups, like GROUP BY but without collapsing them. It is optional. Omit it and the whole result set is one partition.
• ORDER BY sequences rows inside each partition. It is required for anything order-dependent: running totals, LAG, LEAD, and ranking.
• The frame clause (ROWS or RANGE BETWEEN ...) controls which rows within the partition are in scope for the calculation. Most people never write it explicitly, which is the source of a common bug covered below.

The window functions worth knowing

You can cover almost every analytics need with a small set:

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n)
• Offset: LAG(column, n), LEAD(column, n) to reach earlier or later rows
• Aggregates as windows: SUM(), AVG(), COUNT(), MIN(), MAX() with an OVER clause
• Positional: FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

Business questions window functions answer

The patterns below map directly to questions people ask in dashboards and reports.

Running total of revenue over time

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue
FROM daily_sales;

The ROWS frame accumulates row by row, so each day shows revenue to date.

Month-over-month growth

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;

LAG pulls the previous month’s value into the current row so you can subtract and divide. Swap in LAG(revenue, 12) for year-over-year.

Most recent record per group (and deduplication)

SELECT *
FROM (
  SELECT
    orders.*,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

ROW_NUMBER() numbers each customer’s orders newest first, then the outer filter keeps only the latest. The same pattern deduplicates: keep rn = 1 to drop repeats.

Rank customers by lifetime spend

SELECT
  customer_id,
  total_spend,
  RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
FROM customer_totals;

Seven-day moving average

SELECT
  day,
  signups,
  AVG(signups) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_signups;

The explicit frame is the moving window: the current row plus the six before it.

Share of total

SELECT
  category,
  revenue,
  ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM category_revenue;

An empty OVER () treats every row as one partition, so the denominator is the grand total.

ROW_NUMBER vs RANK vs DENSE_RANK

These three look interchangeable until there is a tie, and the difference matters when you build leaderboards or pick a single winner. Given four rows with scores 100, 90, 90, 80:

Use ROW_NUMBER() when you need exactly one row per group. Use RANK() or DENSE_RANK() when ties should genuinely tie. If ranking on a column with duplicates, add a tiebreaker to ORDER BY (for example ORDER BY total_spend DESC, customer_id) so ROW_NUMBER() is deterministic across runs.

The mistakes that produce wrong numbers

Window functions fail quietly. The query runs and returns a number; it is just the wrong one.

• Filtering on a window result in WHERE. Window functions are evaluated after WHERE, so you cannot reference the computed column there. Wrap the query in a CTE or subquery and filter in the outer query. Snowflake, BigQuery, Databricks, and DuckDB also support a QUALIFY clause that filters window results inline; PostgreSQL and MySQL do not, so use the subquery approach there.
• The default frame surprise. When you add ORDER BY without an explicit frame, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With duplicate values in the ORDER BY column, RANGE lumps all tied rows together, so a “running total” jumps by an entire day at once instead of accumulating row by row. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a true running total.
• LAST_VALUE() returning the current row. Because of the same default frame, LAST_VALUE(x) OVER (ORDER BY y) returns the current row, not the last row in the partition. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to look at the whole partition.
• Forgetting PARTITION BY. A running total without a partition accumulates across every customer instead of resetting per customer.
• Performance on large tables. Window functions sort within each partition, which is expensive over millions of rows. Pre-aggregate first, filter to the range you need, and make sure the PARTITION BY and ORDER BY columns are supported by an index or clustering.

When not to reach for a window function

Window functions are not always the right tool.

• If you only need one summarized row per group and never the detail, GROUP BY is simpler and usually faster.
• For a plain filter, use WHERE. Do not compute a rank just to keep the top row when a MAX or a LIMIT will do.
• For recursive hierarchies (an org chart, a category tree), use a recursive CTE, not a window function.

Where window functions run

Window functions are part of the ANSI SQL standard and run wherever you write SQL: PostgreSQL, SQL Server, Snowflake, BigQuery, Amazon Redshift, and ClickHouse, plus MySQL 8.0 and later, which added them after years without. That portability is the point. The same running-total or month-over-month query works against your production database or your warehouse.

A BI tool that runs SQL against your live data lets you turn these queries into charts and shared views without exporting to a spreadsheet. In Basedash, you can run a window-function query against a connected PostgreSQL, Snowflake, or BigQuery source, save it as a view, and let non-technical teammates ask follow-up questions in plain language, which the tool translates back into SQL. Keeping the calculation in the query, rather than a spreadsheet formula, means the running total or rank refreshes when the data does. For a sanity check on machine-written SQL, see how to review AI-generated SQL.

FAQ

Do window functions work in every SQL database?

They are part of the SQL standard and are supported in PostgreSQL, SQL Server, Snowflake, BigQuery, Amazon Redshift, ClickHouse, and most modern engines. The main exception to watch for is MySQL, which only added window functions in version 8.0; MySQL 5.7 and earlier do not support them.

What is the difference between a window function and an aggregate function?

An aggregate function with GROUP BY collapses many rows into one. A window function performs a similar calculation but returns a value on every row, so you keep the detail. SUM(amount) with GROUP BY customer_id gives one total per customer; SUM(amount) OVER (PARTITION BY customer_id) gives every order with the customer’s total attached.

Can I use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE, so the column does not exist yet at filter time. Wrap the query in a subquery or CTE and filter in the outer query, or use QUALIFY in engines that support it, such as Snowflake, BigQuery, Databricks, and DuckDB.

What is the difference between ROWS and RANGE in a frame clause?

ROWS counts physical rows relative to the current row, so ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is exactly seven rows. RANGE works on the values in the ORDER BY column and groups rows with the same value together. For most running totals and moving averages you want ROWS, because RANGE can pull in more rows than you expect when values repeat.

Are window functions slow?

They add a sort within each partition, so they cost more than a plain scan, but they are usually far faster than doing the same work with self-joins or in application code. On large tables, filter and pre-aggregate before the window step, and index the PARTITION BY and ORDER BY columns. See how to make slow BI dashboards fast for the broader performance playbook.