SQL Window Functions: Frames, Ranking, and Examples

What are SQL window functions?

A window definition can contain PARTITION BY, window ORDER BY, and a frame. Partitioning decides where calculations restart. Ordering defines sequence and peers. The frame decides which rows around the current row are visible to frame-sensitive calculations. Treat these as separate design choices.

window_function(expression) OVER (
  PARTITION BY group_key
  ORDER BY sequence_key, unique_tie_breaker
  ROWS BETWEEN frame_start AND frame_end
)

Choose the right window function family

Window functions solve different questions. Ranking functions describe position; offset functions inspect another row; value functions read values at frame boundaries; aggregate functions calculate over a window rather than collapsing a group. Choosing by question prevents complex expressions from being used where a simpler group or join is clearer.

Separate partition boundaries from row ordering

PARTITION BY customer_id creates an independent customer calculation. Omitting it makes the whole filtered result one partition. Window ORDER BY defines logical order inside each partition but does not guarantee final presentation order. Add a query-level ORDER BY when the delivered rows must be sorted.

SELECT
  order_id, customer_id, order_date, amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
  ) AS order_sequence
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_sequence;

The unique order_id resolves equal timestamps. Without a tie-breaker, functions that need a unique sequence may choose peer order unpredictably. Peer-aware functions such as RANK intentionally treat equal ordering values together, so the business meaning of ties should determine the function.

Define window frames instead of trusting defaults

A frame is the subset of the partition visible to the current row for frame-sensitive functions. Common units are ROWS, RANGE, and, where supported, GROUPS. ROWS counts physical result rows. RANGE considers ordering values and peers. GROUPS counts peer groups. Dialect support and restrictions vary.

SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date, order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue

With duplicate ordering values, an implicit RANGE-like default can include all peers and make a running total jump by several rows. Explicit ROWS plus a unique ordering key creates row-by-row progression. For a moving seven-row average, use a bounded row frame; for seven calendar days, row count is not equivalent to time and a supported interval range or time-spine design may be required.

Apply window functions to real analytical patterns

SELECT
  region_id, month_start, revenue,
  revenue / NULLIF(
    SUM(revenue) OVER (
      PARTITION BY month_start
    ), 0
  ) AS share_of_month,
  revenue - LAG(revenue) OVER (
    PARTITION BY region_id
    ORDER BY month_start
  ) AS change_from_prior_month
FROM region_monthly_revenue;

Filter window results at the correct query level

Window functions are logically evaluated after the same-level WHERE, grouping, aggregation, and HAVING. Most dialects therefore cannot reference a window alias in that same level's WHERE. Compute the value in a subquery or CTE, then filter outside. Platforms supporting QUALIFY offer a direct window-result filter.

WITH ranked_products AS (
  SELECT
    category_id, product_id, revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC, product_id
    ) AS rn
  FROM product_revenue
)
SELECT *
FROM ranked_products
WHERE rn <= 2;

Filter placement changes the population. Filtering inactive products inside the CTE ranks only active products. Ranking all products and filtering activity outside asks whether the already-ranked winner happens to be active. Both are valid SQL; they answer different questions.

Reduce repeated work across multiple windows

Several functions with the same partition and order can often share a named window or compatible sort. Functions using different ordering keys may require additional sort passes. A query ranking by revenue, sequencing by time, and calculating a moving average by another timestamp may therefore perform several expensive ordered operations even though each expression is short.

SELECT
  account_id, event_time, amount,
  SUM(amount) OVER w AS running_amount,
  LAG(amount) OVER w AS previous_amount,
  ROW_NUMBER() OVER w AS event_number
FROM account_events
WINDOW w AS (
  PARTITION BY account_id
  ORDER BY event_time, event_id
);

Named windows improve consistency and reduce copy-paste drift, but do not guarantee one physical sort. Inspect the actual plan. Also narrow rows before large sorts when semantics allow; carrying wide text or JSON columns through window operations increases memory and spill risk.

Review window-function performance with evidence

Performance depends on filtered row volume, partition distribution, sorting keys, existing order, memory, parallelism, indexes, and engine behavior. One huge partition can dominate memory even when average partitions are small. Nonselective joins before the window can multiply rows and make every later sort more expensive.

The PostgreSQL window-function reference documents ranking, offsets, value functions, peer groups, and frame-sensitive behavior. Use official documentation for the deployed engine because frame features and null-handling options vary.

Validate window logic before production

1. Define the input grain. Audit joins and filters before window evaluation.
2. State the partition. Explain why calculation restarts at that business boundary.
3. Make order complete. Add stable tie-breakers where row sequence matters.
4. Specify the frame. Test peers, first rows, last rows, sparse time periods, and empty frames.
5. Check filter placement. Decide whether eligibility applies before or after the window result.
6. Compare functions. Test ROW_NUMBER, RANK, and DENSE_RANK at tie boundaries when selecting top N.
7. Profile partitions. Measure largest groups, not only averages.
8. Inspect the plan. Review sorts, spills, repeated passes, estimates, and actual rows.

Review window functions as a complete analytical system in production

Production review should follow the data from source grain to final consumer rather than examining an isolated OVER clause. Record which rows enter each partition, which columns make ordering deterministic, which frame boundaries apply, and whether later joins or filters can duplicate or remove ranked rows. A technically valid window can still answer the wrong question when upstream data contains repeated events, late-arriving corrections, or multiple current records.

Build a small adversarial fixture before testing at scale. Include two rows tied on the visible sort key, a null ordering value, a one-row partition, a partition with duplicate timestamps, and a correction arriving after the original event. For running totals, add negative adjustments and duplicate business dates. For LAG or LEAD, verify the first and last rows explicitly. For ranking, decide whether ties should share a rank, create gaps, or be broken by a stable identifier.

Finally, reconcile a sample partition manually and preserve that fixture as a regression test. Compare row counts before and after every join, check that the selected “latest” row is unique, and inspect actual memory, sort, spill, and parallelism evidence. Optimization is successful only when the result remains identical for ordinary rows, ties, nulls, sparse partitions, and late corrections.

Document engine-specific behavior too. Default frames, null ordering, support for named windows, and availability of QUALIFY differ across systems. Keep portable business logic separate from dialect shortcuts, and link each shortcut to a tested fallback. This makes migrations and mixed-engine reviews safer without forcing every query into the least capable syntax.

SQL window functions frequently asked questions

Continue with a related guide

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents.

• PARTITION BY in SQL
• ROW_NUMBER in SQL
• RANK in SQL