What is a window function?

A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into a single output row. That is the critical difference from a regular aggregate (SUM, AVG, COUNT used with GROUP BY): an aggregate reduces N rows to 1; a window function returns one value per input row while still seeing the whole "window" of related rows.

Every window function is invoked with an OVER clause. The OVER clause defines three things: the partition (which rows are in scope, via PARTITION BY), the order (how rows are sequenced, via ORDER BY), and optionally the frame (which subset of the partition is visible from the current row, via ROWS or RANGE). If you understand OVER, you understand window functions.

The OVER clause: anatomy and intuition

The OVER clause has three optional sub-clauses, applied in this order:

1. PARTITION BY <expr> — splits the input into independent groups; the function restarts on each new group.
2. ORDER BY <expr> — orders rows inside each partition; required for ranking, LAG/LEAD and any cumulative calculation.
3. ROWS or RANGE BETWEEN <start> AND <end> — defines the window frame relative to the current row.

SELECT
  department_id,
  employee_id,
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM employees;

Reading that query top to bottom: SUM is the function; OVER opens the window definition; PARTITION BY restarts SUM at each new department; ORDER BY hire_date sequences employees within each department; the ROWS BETWEEN clause says "include every row from the start of this partition up to and including the current row". The result is a per-employee cumulative payroll within each department.

Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ranking functions assign an ordinal value to each row inside a partition, based on the ORDER BY clause. The four standard ranking functions differ in how they handle ties:

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

A near-universal interview question is "deduplicate rows by some key, keeping the most recent". Use ROW_NUMBER() with PARTITION BY <dedupe_key> ORDER BY <freshness_field> DESC, then filter to row_number = 1. ROW_NUMBER is preferred over RANK here because you want exactly one row per group, even if two records tie on the freshness field.

Offset functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Offset functions read a value from another row in the same partition without joining the table to itself. They are how analytical SQL expresses "compared to the previous row" or "compared to the first row".

• LAG(expr, offset, default) — value from a row N positions before the current row.
• LEAD(expr, offset, default) — value from a row N positions after the current row.
• FIRST_VALUE(expr) — value of expr in the first row of the window frame.
• LAST_VALUE(expr) — value of expr in the last row of the window frame (mind the frame default).
• NTH_VALUE(expr, n) — value in the n-th row of the window frame.

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,
  ROUND(
    100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_change_pct
FROM monthly_revenue;

Aggregate functions as window functions

Every standard aggregate (SUM, AVG, COUNT, MIN, MAX, plus statistical aggregates like STDDEV and VAR_POP) can be used as a window function simply by adding an OVER clause. The aggregate keeps its semantics but stops collapsing rows.

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary)   OVER (PARTITION BY department_id) AS dept_avg,
  COUNT(*)      OVER (PARTITION BY department_id) AS dept_size,
  salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

This pattern — "show every row alongside group statistics" — is impossible with GROUP BY without a self-join, and it is the reason window functions exist. If you are reaching for a self-join to add a group statistic to detail rows, replace it with a window aggregate.

PARTITION BY vs GROUP BY: when to use which

A practical rule: if your output should have one row per group, use GROUP BY. If your output should have one row per input row, but you also need group-level statistics on each row, use a window function. The two are complements, not competitors — many production queries use both in the same statement.

Window frames: ROWS vs RANGE

When ORDER BY is present and no explicit frame is specified, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default is fine for SUM and COUNT but wrong for LAST_VALUE and any frame-sensitive function. Always specify the frame explicitly when behaviour matters.

SELECT
  sale_date,
  daily_total,
  AVG(daily_total) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_sales;

Five real-world patterns you will use weekly

1. Deduplicate keeping the most recent record

WITH numbered AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM customer_snapshots
)
SELECT * FROM numbered WHERE rn = 1;

2. Top-N per group

SELECT *
FROM (
  SELECT product_id, category, sales,
         RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS r
  FROM monthly_sales
) t
WHERE r <= 5;

3. Period-over-period delta

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_delta
FROM monthly_revenue;

4. Running total / cumulative sum

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

5. Sessionisation / gap-and-island

WITH gaps AS (
  SELECT user_id, event_at,
         CASE
           WHEN event_at - LAG(event_at) OVER (
                  PARTITION BY user_id ORDER BY event_at
                ) > INTERVAL '30 minutes' THEN 1
           ELSE 0
         END AS new_session
  FROM events
),
numbered AS (
  SELECT user_id, event_at,
         SUM(new_session) OVER (
           PARTITION BY user_id ORDER BY event_at
         ) AS session_id
  FROM gaps
)
SELECT user_id, session_id, MIN(event_at) AS started_at, MAX(event_at) AS ended_at
FROM numbered
GROUP BY user_id, session_id;

Dialect notes: PostgreSQL, MySQL, SQL Server, Oracle, SQLite

• PostgreSQL: full SQL-standard support since 8.4, including all frame modes (ROWS, RANGE, GROUPS) and named windows via WINDOW clause.
• MySQL: window functions added in 8.0 (released 2018). MySQL 5.7 and earlier do NOT support them — you must emulate with self-joins or user variables.
• SQL Server: full support since 2012; added LAG/LEAD/PERCENTILE_CONT in 2012, FIRST_VALUE/LAST_VALUE since 2012, OVER() with RANGE since 2017.
• Oracle: comprehensive support since 8i (1999) — Oracle was actually the reference implementation for SQL:2003 window functions.
• SQLite: window functions added in version 3.25 (2018). Sufficient for most workloads; no GROUPS frame mode.

Performance: when window functions are fast (and when they are not)

Window functions are evaluated AFTER the WHERE, GROUP BY and HAVING clauses but before the final ORDER BY. The query planner needs to sort each partition once; if PARTITION BY and ORDER BY align with an existing index, that sort can be skipped.

• A composite index on (partition_columns, order_columns) lets the planner stream rows in window order without an explicit sort step.
• Multiple window functions sharing the same OVER specification are computed in a single pass — write them with identical OVER clauses (or use named windows) to avoid redundant sorts.
• Filtering on a window-function result requires an outer query or CTE — `WHERE ROW_NUMBER() OVER (...) = 1` is illegal because WHERE runs before window evaluation.
• For very large partitions, ROWS BETWEEN frames are typically faster than RANGE BETWEEN with date intervals, because ROWS uses a fixed-size buffer.

Practice: 15 hands-on lessons in the Window Functions module

Each topic above maps to a hands-on lesson in the Learning Mode curriculum. Lessons include runnable PostgreSQL examples, exercises against the HR, E-commerce and Banking schemas, and progressive difficulty from intermediate to expert.