Step-by-Step Guide to SQL Window Functions for Beginners

A window function is an aggregate-style calculation performed across a set of rows related to the current row, where that set — the “window” — is defined by an OVER clause rather than a GROUP BY. Unlike GROUP BY, the window does not collapse the input rows. Each row keeps its own identity, and the calculated value from the surrounding window is appended to it as an extra column.

This guide walks through window functions as a sequence of steps. Each step introduces one new piece, shows the syntax, and states what you should expect in the output before you run it. By the end, you will have touched OVER, PARTITION BY, ORDER BY inside the window, the three ranking functions, and the offset functions LAG and LEAD.

As a running example, assume a table named sales:

CREATE TABLE sales (
  sale_id     INT PRIMARY KEY,
  salesperson TEXT,
  region      TEXT,
  sale_date   DATE,
  amount      NUMERIC(10,2)
);

Sample data for the walkthrough:

Step 1: Run a Plain Aggregate First, and Note What It Loses

Before touching window functions, run the GROUP BY version of the problem so you see the behavior you are about to improve on.

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;

Expected output:

Six input rows become three output rows. The individual sale_id and sale_date values are gone. That row loss is the specific limitation window functions remove. If the summary-only output is all your report needs, stop here — GROUP BY is the simpler and more appropriate tool, and adding a window function to this query would only add complexity.

Step 2: Add the Smallest Possible OVER Clause

The minimal window function is an aggregate followed by OVER () with nothing inside the parentheses. An empty OVER means: every row in the result set belongs to a single window.

SELECT
  sale_id,
  salesperson,
  amount,
  SUM(amount) OVER () AS grand_total
FROM sales;

Expected output (order may vary by engine):

Two things to verify here: the row count is still six, and grand_total repeats the same value on every row. That repetition is the core mechanic. The window produced one number, and the window function copied it onto every row inside that window.

Step 3: Introduce PARTITION BY to Split the Window

PARTITION BY is the keyword that breaks one big window into separate windows, one per distinct value of the listed column. Add it inside the OVER parentheses.

SELECT
  sale_id,
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY salesperson) AS person_total
FROM sales;

Expected output:

Verification: Ada’s two rows both show 300.00, matching the GROUP BY result from Step 1, but the two rows are still present with their sale_id and amount intact. You can now see row-level detail and group-level totals in the same row of output, which is the whole reason window functions exist.

Step 4: Add ORDER BY Inside the Window for Running Totals

Putting ORDER BY inside the OVER clause changes the window from “the whole partition” to “the partition up to and including the current row,” based on the ordering. This is what produces running totals.

SELECT
  salesperson,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
  ) AS running_total
FROM sales
ORDER BY salesperson, sale_date;

Expected output:

Ada’s second row shows 300.00, not 300.00 twice bundled — it accumulates 100 then 300. Without ORDER BY inside the window, SUM(amount) OVER (PARTITION BY salesperson) returns the full partition total on every row instead of a running figure. The ORDER BY is what makes the accumulation happen.

Step 5: Assign Row Numbers Within Each Group

ROW_NUMBER() takes no arguments inside its own parentheses. It numbers rows 1, 2, 3, and onward within each partition, following the window’s ORDER BY.

SELECT
  salesperson,
  sale_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY salesperson
    ORDER BY sale_date DESC
  ) AS recency_rank
FROM sales;

Expected output:

Filtering recency_rank = 1 returns each salesperson’s most recent sale. This is the “top one row per group” pattern. To apply the filter you need an outer query — window functions cannot be referenced in the same query’s WHERE clause because WHERE runs before window functions are evaluated.

SELECT salesperson, sale_date, amount
FROM (
  SELECT
    salesperson,
    sale_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY salesperson
      ORDER BY sale_date DESC
    ) AS recency_rank
  FROM sales
) ranked
WHERE recency_rank = 1;

Step 6: Handle Ties With RANK and DENSE_RANK

ROW_NUMBER always produces unique numbers, even when the ordering column has duplicate values. RANK and DENSE_RANK give tied rows the same number, but differ in what happens next.

• RANK: tied rows share a rank, and the next distinct value skips ahead by the number of tied rows.
• DENSE_RANK: tied rows share a rank, and the next distinct value takes the immediately following integer.

SELECT
  sale_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
  RANK()       OVER (ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM sales;

Expected output:

Observation: RANK shows no value of 2, because the two-way tie at 500 consumed positions 1 and 2. DENSE_RANK produces no such gap. Pick ROW_NUMBER when you need exactly one row per group and ties are an acceptable arbitrary choice; pick RANK when tied rows should visibly share a position and you want the gap to signal how many rows tied; pick DENSE_RANK when you want shared positions without gaps — for example, “top 3 distinct selling amounts” reporting.

Step 7: Compare a Row to Its Neighbors With LAG and LEAD

LAG and LEAD read a column value from a different row inside the same window. They require both PARTITION BY and ORDER BY in the OVER clause to be useful, because “previous” and “next” are only defined relative to a sort order.

SELECT
  salesperson,
  sale_date,
  amount,
  LAG(amount)  OVER (PARTITION BY salesperson ORDER BY sale_date) AS prev_amount,
  LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY salesperson, sale_date;

Expected output:

The first row of each partition has no previous row, so prev_amount is NULL. The last row has no follower, so next_amount is NULL. From here, month-over-month or week-over-week deltas are a simple subtraction: amount - prev_amount.

LAG and LEAD accept two optional arguments: an offset (default 1) and a default value to return instead of NULL. LAG(amount, 1, 0) returns 0 on the first row of each partition instead of NULL, which is useful when a downstream calculation cannot tolerate NULL.

Step 8: Verify the Result Set

After each of the queries above, run a quick sanity check before trusting the output:

1. Confirm the row count. A window function should never change the number of rows returned by the SELECT it wraps, unless you deliberately filter on the window function’s alias in an outer query.
2. Confirm partition boundaries. The last row of each partition should be the last one in the window’s ORDER BY, and its cumulative value should equal the GROUP BY total for that partition.
3. Confirm NULL placement. LAG returns NULL on the first row of a partition, not the first row of the entire result set. If you see NULL more often than that, check that PARTITION BY includes the column you intended.

Trade-offs, Failure Modes, and When Not to Use Window Functions

Use GROUP BY instead when you do not need row-level detail. If the only output you want is one summary row per group, a window function adds complexity for no benefit: the window value is computed redundantly on every row of the partition before collapsing.

Watch for ORDER BY inside the window when you did not intend a running total. Adding ORDER BY to SUM(amount) OVER (PARTITION BY salesperson) silently changes the meaning from a partition total to a cumulative total. This is one of the most common subtle mistakes.

Filtering on a window function requires an outer query or a CTE. You cannot write WHERE ROW_NUMBER() OVER (...) = 1 in the same SELECT. Wrap the query in a subquery or a WITH clause and filter outside.

Performance varies widely by engine. Window functions typically require sorting or hashing within each partition. On very large tables with high-cardinality partitions, this can be more expensive than an equivalent GROUP BY. Most database engines will use an index on the PARTITION BY + ORDER BY columns when one exists, so check the query plan if a windowed query is slow.

Not every aggregate works as a window function. Standard aggregates — SUM, COUNT, AVG, MIN, MAX — plus the ranking and offset functions are widely supported. STRING_AGG, ARRAY_AGG, and similar engine-specific aggregates may or may not accept OVER depending on the database and version.

MySQL 8.0 and later, PostgreSQL 8.4 and later, SQL Server 2005 and later, and modern SQLite all support window functions. If you are on MySQL 5.7 or an older SQLite, the syntax above will fail outright — no partial-credit mode exists. Verify your engine version before investing time in a refactor.

Where to Go Next

Once the eight steps above are comfortable, the natural next topics are:

• Named windows (OVER w ... WINDOW w AS (...)) for reusing the same window definition across multiple columns.
• Frame clauses (ROWS BETWEEN ... AND ..., RANGE BETWEEN ... AND ...) when the default frame is not what you want — the default when ORDER BY is present is a running frame, and the default when it is absent is the entire partition.
• NTILE(n) for bucketing rows into n equal groups, commonly used for quartile or decile analysis.

The single habit that predicts success with window functions: before writing a query, decide whether the answer requires the individual rows to remain in the output. If yes, you want a window function. If no, you want GROUP BY. Getting that decision right prevents most of the awkward rewrites that come from choosing the wrong one first.