Name a Window Definition for Reuse

Define a window specification once with a `WINDOW` alias and reference it in multiple `OVER` clauses — avoiding repetition when several window functions share the same partition and ordering.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Rank reps and show the previous rep's revenue using a shared window

WINDOW w AS (PARTITION BY region ORDER BY revenue DESC) defines the window once. Both RANK() OVER w and LAG(revenue) OVER w reference it without repeating the specification. Within the East region Alice ranks first (no previous row, so LAG returns NULL), Eve ranks second with 85000 as the previous revenue, and Bob ranks third with 79000. Within West, Carol ranks first and Dave ranks second with 91000 as the previous revenue. SQL Server achieves the same result but requires the OVER clause to be written out in full for each function.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales_reps (name VARCHAR(50), region VARCHAR(20), revenue INT);

INSERT INTO
  sales_reps
VALUES
  ('Alice', 'East', 85000),
  ('Bob', 'East', 72000),
  ('Carol', 'West', 91000),
  ('Dave', 'West', 68000),
  ('Eve', 'East', 79000);

Validated query

SQL

SELECT
  name,
  region,
  revenue,
  RANK() OVER w AS rnk,
  LAG (revenue) OVER w AS prev_revenue
FROM
  sales_reps
WINDOW
  w AS (
    PARTITION BY
      region
    ORDER BY
      revenue DESC
  )
ORDER BY
  region,
  rnk;

Expected result

name	region	revenue	rnk	prev_revenue
Alice	East	85000	1	NULL
Eve	East	79000	2	85000
Bob	East	72000	3	79000
Carol	West	91000	1	NULL
Dave	West	68000	2	91000

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales_reps (name VARCHAR(50), region VARCHAR(20), revenue INT);

INSERT INTO
  sales_reps
VALUES
  ('Alice', 'East', 85000),
  ('Bob', 'East', 72000),
  ('Carol', 'West', 91000),
  ('Dave', 'West', 68000),
  ('Eve', 'East', 79000);

Validated query

SQL

SELECT
  name,
  region,
  revenue,
  RANK() OVER (
    PARTITION BY
      region
    ORDER BY
      revenue DESC
  ) AS rnk,
  LAG (revenue) OVER (
    PARTITION BY
      region
    ORDER BY
      revenue DESC
  ) AS prev_revenue
FROM
  sales_reps
ORDER BY
  region,
  rnk;

Expected result

name	region	revenue	rnk	prev_revenue
Alice	East	85000	1	NULL
Eve	East	79000	2	85000
Bob	East	72000	3	79000
Carol	West	91000	1	NULL
Dave	West	68000	2	91000

MySQL, MariaDB, PostgreSQL, and SQLite define the window with `WINDOW w AS (...)` and reference it with `OVER w`. SQL Server must repeat the full `OVER (PARTITION BY region ORDER BY revenue DESC)` on each function.

Useful when

Where this command helps.

applying rank, lag, and running total window functions that all share the same partition and order without repeating the OVER clause
refactoring a query with many window functions to remove duplicated OVER specifications

Explanation

What the command is doing.

When a query uses several window functions that all share the same PARTITION BY and ORDER BY specification, repeating the full OVER (PARTITION BY ... ORDER BY ...) expression on each function is verbose and error-prone. The WINDOW clause at the end of the SELECT statement lets you assign a name to a window definition; each aggregate or window function then references that name with OVER w instead of spelling out the full specification. If one function needs a slightly different frame or ordering, a named window can also be extended inline: OVER (w ROWS BETWEEN ...). SQL Server does not support the WINDOW clause and requires the full OVER specification to be repeated on each function.

Name a Window Definition for Reuse

Rank reps and show the previous rep's revenue using a shared window

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.