SQL Window Functions Explained With Practical Examples

SQL window functions calculate a value for every row using a set of related rows, called the window, without collapsing those rows the way GROUP BY does. That one property lets you rank rows, compare a row with the one before it, and compute running totals or moving averages in a single readable query. This guide walks through SQL window functions with practical PostgreSQL examples, including the frame rules that trip people up in interviews and in production dashboards.

What are SQL window functions?

A window function is any function followed by an OVER clause. The OVER clause describes which rows the function can see while it computes the value for the current row. It has three optional parts:

function_name(arguments) OVER (
  PARTITION BY partition_columns   -- split rows into independent groups
  ORDER BY sort_columns            -- order rows inside each group
  frame_clause                     -- which ordered rows count for this row
)

Every example below uses one small table, orders(order_id, customer_id, order_date date, amount numeric). If you want a refresher on the basics first, the SQL guide covers SELECT, filtering, and aggregation.

PARTITION BY vs GROUP BY

Both split rows into groups, but they return very different shapes. GROUP BY returns one row per group. PARTITION BY keeps every input row and attaches the group-level result to each one.

Here is each order alongside its customer's total and its share of that total:

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
  ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;

An empty OVER () treats the whole result set as one window, so amount / SUM(amount) OVER () gives each row's share of the grand total.

ROW_NUMBER vs RANK vs DENSE_RANK

All three assign positions based on the ORDER BY inside OVER. They differ only in how they treat ties. Suppose one customer has orders of 500, 500, and 300:

• ROW_NUMBER always produces unique numbers. Which tied row gets 1 is arbitrary unless you add a tiebreaker.
• RANK gives ties the same number, then skips ahead, like sports standings.
• DENSE_RANK gives ties the same number without gaps, which is what you want for "second highest distinct value" questions.

How to get the top N rows per group

Window functions are evaluated after WHERE, GROUP BY, and HAVING, so you cannot filter on them directly in WHERE. PostgreSQL does not support the QUALIFY clause that some warehouses offer, so wrap the query in a CTE:

WITH ranked AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC, order_id
    ) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked
WHERE rn <= 3;

The order_id tiebreaker makes the result deterministic. Without it, two runs over the same data can return different rows when amounts tie. The same pattern, with ORDER BY updated_at DESC and rn = 1, is the standard way to deduplicate a table down to the latest record per key.

LAG and LEAD: comparing a row with its neighbors

LAG(expr, offset, default) reads a value from an earlier row in the window; LEAD reads from a later row. The offset defaults to 1 and the default value to NULL. A named WINDOW clause keeps repeated definitions tidy:

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  revenue,
  LAG(revenue) OVER w AS prev_revenue,
  revenue - LAG(revenue) OVER w AS change,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER w)
    / NULLIF(LAG(revenue) OVER w, 0),
    1
  ) AS pct_change
FROM daily
WINDOW w AS (ORDER BY order_date)
ORDER BY order_date;

Two details matter here. First, LAG returns the previous row, not the previous calendar day. If a day has no orders, the comparison silently skips it, so join to a calendar table when gaps are possible. Second, NULLIF avoids a division-by-zero error when the prior value is zero.

With a partition, the same idea measures time between events per customer. In PostgreSQL, subtracting one date from another returns an integer number of days:

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS days_since_previous_order
FROM orders;

Running totals and moving averages

Aggregate functions such as SUM and AVG become window functions when you add OVER. The frame clause decides which rows are included for each row:

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_rows
FROM daily
ORDER BY order_date;

For the first six rows the moving average covers fewer than seven values, because there is nothing earlier to include. If you only want complete windows, also compute COUNT(*) OVER the same frame and keep rows where it equals 7. For a running total that resets each month, add PARTITION BY date_trunc('month', order_date).

ROWS vs RANGE: the window frame trap

This is the part most people get wrong. When you write ORDER BY inside OVER and omit the frame, the SQL standard (and PostgreSQL) uses a default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as peers and includes all of them together.

Consider a running total over individual orders ordered by order_date, where two orders share a date:

Neither answer is wrong, but they answer different questions. ROWS counts physical rows, so it gives a strictly increasing running sum, but the order within ties is arbitrary. RANGE gives "total as of the end of this date". Pick one explicitly instead of relying on the default.

RANGE also accepts value offsets in PostgreSQL 11 and later, which is the correct way to build a calendar-based window when dates have gaps:

SELECT
  order_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_days
FROM daily_revenue;

PostgreSQL also supports a GROUPS frame mode, which counts peer groups rather than rows or values.

The LAST_VALUE gotcha

Because the default frame ends at the current row, LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY order_date) returns the current row's value (or its last peer), not the last value in the partition. Extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use FIRST_VALUE with the ordering reversed.

Do window functions hurt performance?

A window function usually requires sorting rows by the partition and order keys. Windows that share the same PARTITION BY and ORDER BY can reuse one sort, so defining them consistently helps. On large tables, an index that matches the sort, such as (customer_id, order_date), can let the planner skip an explicit sort; the database indexes guide explains when that works. Always check the plan with EXPLAIN ANALYZE, and see the query execution guide for how to read it.

Common SQL window function interview questions

1. Nth highest value per group: DENSE_RANK partitioned by the group, then filter in an outer query.
2. Deduplicate to the latest record: ROW_NUMBER ordered by timestamp descending, keep rn = 1.
3. Month-over-month growth: aggregate by month, then LAG.
4. Consecutive-day streaks (gaps and islands): subtract ROW_NUMBER() days from each date; rows in the same streak share the same result.
5. Percent of total: divide by SUM(x) OVER ().
6. Retention by cohort: combine MIN(...) OVER (PARTITION BY user_id) with date differences, as shown in cohort and retention analysis in SQL.

Key takeaways

• Window functions add a computed column to every row instead of collapsing rows like GROUP BY.
• Choose ROW_NUMBER, RANK, or DENSE_RANK based on how ties should behave, and add a tiebreaker for deterministic results.
• LAG and LEAD compare against neighboring rows, not neighboring dates; fill date gaps with a calendar table.
• With ORDER BY and no frame, the default is RANGE, which groups tied rows; state ROWS or RANGE explicitly.
• Filter on window results in an outer query or CTE, because they are computed after WHERE.

Frequently asked questions

Can I use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE, GROUP BY, and HAVING, so their results do not exist yet when WHERE runs. Compute them in a CTE or subquery and filter in the outer query. Some warehouses provide QUALIFY for this, but PostgreSQL does not.

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same number. RANK then skips positions, so ranks go 1, 1, 3, while DENSE_RANK continues without gaps, going 1, 1, 2. Use DENSE_RANK when you need the Nth distinct value.

Can I combine GROUP BY and window functions in one query?

Yes. The GROUP BY runs first and produces grouped rows, then the window function runs over those grouped rows. That is why SUM(SUM(amount)) OVER () is valid: the inner SUM is the group aggregate and the outer SUM is the window.

Why does my running total jump for rows with the same date?

You are using the default RANGE frame, which includes all peer rows that share the same ORDER BY value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a row-by-row total, plus a tiebreaker column in ORDER BY to make the order stable.