SQL Window Functions: Example Queries and Cheat Sheet

A SQL window function calculates a value from related rows while keeping each input row in the result. Use OVER to define the window, PARTITION BY to calculate independently within groups, and ORDER BY to set calculation order. For a row-by-row running total, specify an explicit ROWS frame and a deterministic sort order.

The examples below are illustrative SQL patterns, not tested queries. Window-function syntax and supported frame features vary by database and version, so check your engine’s documentation before adapting less-portable details.

1. Window functions at a glance

A window is the set of rows a function can use for the current result row. Unlike a grouped aggregate, which generally condenses multiple input rows into one, a window calculation adds a value alongside the rows it analyzed.

Clause	Purpose	Example
PARTITION BY	Starts a separate calculation for each group.	PARTITION BY customer_id
ORDER BY	Sets the calculation sequence or defines ranking peers.	ORDER BY order_date, order_id
Frame	Limits rows available to frame-sensitive calculations around the current row.	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Basic shape:

function_name(arguments) OVER (
  PARTITION BY grouping_column
  ORDER BY sort_column, unique_tie_breaker
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Each part is optional where the task and dialect allow it. Without PARTITION BY, the query rows belong to one partition. A window’s ORDER BY controls calculation order; it does not guarantee the final displayed order. Add an outer ORDER BY when output order matters.

2. Core example queries

Running total per customer

Use a partition per customer, order transactions chronologically, and state a ROWS frame for row-by-row accumulation. The unique order ID breaks ties between orders on the same date.

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

If dates can tie and the order is not made deterministic, the database may process tied rows in different orders. That can change intermediate running totals even if the final total is unchanged.

Rank rows and preserve ties

SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC, employee_id
  ) AS row_num,
  RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS salary_rank,
  DENSE_RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS dense_salary_rank
FROM employees
ORDER BY department_id, salary DESC, employee_id;

ROW_NUMBER assigns a distinct sequence number, so a unique tie-breaker makes the result stable. RANK gives tied values the same rank and leaves gaps afterward. DENSE_RANK gives tied values the same rank without gaps. Keep the ranking expression’s order focused on the value whose ties should count; adding a unique ID to RANK’s ordering would break salary ties.

Top three employees per department

Calculate row numbers in a CTE, then filter in the outer query. Window results are generally not available to WHERE at the same query level because that filtering stage precedes the window calculation.

WITH ranked AS (
  SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC, employee_id
    ) AS rn
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rn <= 3
ORDER BY department_id, rn;

This returns at most three rows in each department. If the requirement is “include everyone tied within the top three ranks,” use RANK or DENSE_RANK instead, and choose the threshold to match the desired tie behavior.

Read the previous row with LAG

SELECT
  account_id,
  transaction_date,
  transaction_id,
  amount,
  LAG(amount) OVER (
    PARTITION BY account_id
    ORDER BY transaction_date, transaction_id
  ) AS previous_amount
FROM transactions
ORDER BY account_id, transaction_date, transaction_id;

The first row in each account partition has no previous row, so the result is typically NULL unless a default is supplied. Offset and default argument syntax can vary; check the target engine’s function reference.

3. Cheat sheet: choose the function and frame

Need	Pattern	Check
Unique sequence within a group	ROW_NUMBER()	Add a unique tie-breaker for stable numbering.
Rank with ties and gaps	RANK()	Rows equal on the window order are peers.
Rank with ties and no gaps	DENSE_RANK()	Confirm support in the engine.
Running sum or average	SUM(x) OVER (...), AVG(x) OVER (...)	Use explicit ROWS if accumulation should advance one row at a time.
Previous or next value	LAG(x), LEAD(x)	Check offset, default, and edge behavior.
First or last value in a frame	FIRST_VALUE(x), LAST_VALUE(x)	Frame boundaries affect which rows are considered.
Filter top N per group	Rank in a CTE or subquery, then filter outside	Decide whether tied rows should expand the result.
Full-partition total on every row	SUM(x) OVER (PARTITION BY group_col)	Avoid ordering if it is unnecessary for the intended total.

Aggregate functions such as SUM can act as window functions by adding OVER. Ranking and offset/value functions have their own rules, so a frame clause appropriate for a cumulative sum may not apply to them.

4. ROWS versus RANGE, and why defaults matter

A frame selects part of the current partition for a frame-sensitive function. When an ordered window omits an explicit frame, common defaults include the partition start through the current row and its peers. Consequently, rows with equal ordering values can share a cumulative aggregate result.

• ROWS counts individual rows relative to the current row.
• GROUPS counts peer groups, where peers share the window ordering values.
• RANGE relates boundaries to ordering values and peer behavior; supported boundary forms and details depend on the engine.

For a row-by-row cumulative sum, use an explicit frame such as:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Also include a unique tie-breaker in the window ordering when the row sequence matters. For a full-partition total repeated on each row, omit window ordering when it is not needed, or specify the intended full frame using syntax supported by your dialect.

Do not assume every engine supports every frame type or boundary form. PostgreSQL documents its default ordered frame and its effects; SQLite describes ROWS, GROUPS, and RANGE boundaries and peers in detail.

5. Query placement, named windows, and dialect notes

PostgreSQL documents window calculations as occurring after filtering and grouping stages and allows window calls in the select list and query ordering. That is why a CTE or subquery is the portable pattern for filtering by a window result. Syntax availability still depends on engine and version.

PostgreSQL 18’s tutorial covers partitions, ordering, default frames, named windows, and filtering through a subquery. SQLite’s official documentation covers aggregate and built-in window functions, peers, frame types, and named windows. MySQL 8.4 documents OVER and aggregates used as window functions. Microsoft’s WINDOW reference applies to SQL Server 2022 (16.x) and later and lists Azure SQL and Fabric contexts; consult its separate OVER reference for frame behavior and ranking-function restrictions.

For a named window, where supported, define a reusable specification and refer to it for multiple calculations. For example, PostgreSQL and SQLite document named windows; verify the syntax and support for your target release before using it.

• PostgreSQL 18: Window Functions tutorial
• SQLite: Window Functions
• MySQL 8.4: Window Function Concepts and Syntax
• Microsoft: WINDOW clause (Transact-SQL)
• Microsoft: OVER clause (Transact-SQL)

6. A practical workflow for writing a window query

1. Write the desired output grain. Decide whether the result should still contain every source row, one row per group, or only a top-N subset.
2. Choose the partition. List the columns that define independent calculations. Leave out PARTITION BY only when the whole result should be one group.
3. Choose the calculation order. Use the business sequence, such as timestamp then transaction ID. Include a unique tie-breaker when reproducibility matters.
4. Choose the function. Use a ranking function for position, an aggregate for cumulative or partition summaries, and an offset function to inspect neighboring rows.
5. Define the frame where it matters. For a cumulative aggregate, decide whether boundaries count rows, peer groups, or values; use explicit bounds to avoid relying on a default.
6. Filter in an outer query. Put window output in a CTE or subquery, then use an outer WHERE for top-N or other filtering.
7. Validate on tie and boundary cases. Try equal sort values, the first row in a partition, null values, and groups smaller than the requested N.
8. Sort the final output. Add the outer ORDER BY needed by the consumer.

7. Edge cases, performance, and reliability

Ties and missing values

Tied sort keys are peers for ranking and can share a default RANGE frame. Decide whether ties should share a rank, share a cumulative result, or receive a stable row order. Null ordering and treatment of null values vary among engines and expressions; specify the intended behavior where the dialect supports it, and verify it with representative rows.

Empty, short, or irregular partitions

Offset functions have no preceding or following row at partition boundaries. A partition can contain fewer rows than a top-N threshold, in which case all available rows qualify. Date gaps matter too: a frame over ordered rows does not automatically mean a calendar-day interval. For rolling periods, check the engine’s value-based frame syntax and data types, or join against a calendar structure if that better expresses the requirement.

Performance checks

• Filter irrelevant source rows before the window when doing so preserves the intended calculation. Filtering early can change a running total’s starting point.
• Partition and order on the columns required by the calculation; avoid extra sort keys that alter peer groups.
• Several window expressions with the same partition and order may be candidates for shared work, but the optimizer’s behavior is engine-specific.
• Large partitions or sorts can require substantial memory or temporary storage. Check the execution plan and workload on the target system; no universal speed guarantee follows from using a window function.
• Use a supporting index only after inspecting the actual plan and write workload. Index usefulness depends on the engine and query shape.

Reliability checklist

• Is the output grain still what downstream code expects?
• Are ordering keys deterministic where row order affects results?
• Do tied rows produce the intended rank and frame behavior?
• Are first-row, last-row, null, and small-partition cases handled?
• Has the exact syntax been checked against the engine and version in production?

8. Troubleshooting common mistakes

Symptom	Likely cause	Fix
“Window function not allowed in WHERE” or equivalent	Filtering is attempted at the same query level as the window calculation.	Calculate in a CTE or subquery and filter from the outer query.
A running total jumps for several tied rows	The default ordered frame includes peers, or the ordering does not uniquely sequence rows.	Use an explicit ROWS frame and add a unique tie-breaker when row-wise accumulation is intended.
Top three returns more or fewer rows than expected	The query uses a ranking function with tie behavior different from the requirement, or the group is smaller than three.	Choose ROW_NUMBER for at most N rows; use RANK or DENSE_RANK when tied ranks should qualify together.
Row numbers change between runs	The window order has duplicate values and no deterministic tie-breaker.	Add a stable unique key to the ordering.
LAST_VALUE appears to return the current row’s value	The frame may end at the current row or its peers rather than the partition end.	Define a frame through the intended last row, using syntax supported by the database.
Syntax error near GROUPS, a named window, or a frame bound	The database version or dialect may not support that feature or form.	Consult the exact version’s reference and use a supported alternative.
A rolling time window includes unexpected rows	Rows-based boundaries count records, not necessarily elapsed time; peer and value-based behavior may differ.	Choose the appropriate frame type and validate with duplicate and missing timestamps.

9. Or skip the browser setup

For a query tutorial or internal runbook, developers sometimes need a screenshot of a reference page. ScreenshotNeo is a website screenshot API and MCP server from ScreenshotNeo. For this SQL article, the call can capture a database documentation page as an image:

ScreenshotNeo API documentation

curl -G "https://api.screenshotneo.com/v1/shot" \
  -d access_key=YOUR_API_KEY \
  --data-urlencode url=https://www.postgresql.org/docs/18/tutorial-window.html \
  -o postgres-window-functions.webp

Its clean-shot flow accepts cookie or consent banners like a visitor and removes more than 60 known consent platforms, newsletter popups, and chat widgets before capture; each step can be turned off. Bot checks or CAPTCHAs, blank pages, timeouts, failed loads, and cache hits cost nothing, and response headers report the page verdict and billing status. Its MCP server offers take_screenshot, get_page_info, and capture_pdf for Claude, Cursor, and other MCP clients.

The free plan includes 1,000 screenshots per month with no card. Paid plans start at $5 for 3,000 shots; Growth is $15 for 15,000, Pro $39 for 60,000, Scale $99 for 250,000, and Business $249 for 1,000,000. Yearly billing gives two months free, and every feature is on every plan. Sign up for 1,000 free screenshots a month, with no card.

10. FAQ

Can I use a window function and GROUP BY in one query?

Yes, but reason about the rows that remain after grouping. A window function in the select list operates on the query’s post-grouping result, so it may see grouped rows rather than the original detail rows. Use a subquery when you need separate aggregation and window stages.

Does OVER always need PARTITION BY?

No. Without it, the rows in the query form one partition. That is useful for a calculation across the whole result, such as a total repeated on each row.

Do window functions make a query faster than a self-join?

There is no engine-independent performance answer. Compare execution plans and representative workloads for the specific query and database.

Where can I get a compact reminder?

Use the cheat-sheet table above to select a function, then confirm frame and syntax details in the documentation for your database version.