Home/Learn/SQL/Window Function Basics

Window Function Basics

Intermediate

Window Functions

Window functions compute a value for each row using a sliding "window" of related rows, without collapsing the result set the way GROUP BY does.

Overview

A window function uses the OVER() clause to define a subset of rows (the window) relative to the current row. PARTITION BY divides rows into groups (like GROUP BY) but keeps all rows visible in the output. ORDER BY inside OVER determines row ordering within each partition for order-sensitive functions. The optional frame clause (ROWS BETWEEN / RANGE BETWEEN) narrows the window further. Critically, window functions execute after WHERE, GROUP BY, and HAVING but before the outer ORDER BY and LIMIT, so you cannot filter on a window result in WHERE — use a CTE or subquery instead.

OVER() Anatomy and PARTITION BY vs GROUP BY

GROUP BY collapses many rows into one summary row per group. PARTITION BY inside OVER() keeps every original row while providing group-scoped computations alongside them. An empty OVER() with no PARTITION BY treats the entire result set as one window.

SQL — GROUP BY vs OVER(PARTITION BY)

-- GROUP BY: collapses rows — one row per user
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

-- Window function: keeps all order rows, adds per-user total as extra column
SELECT
    id,
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total_spent,
    SUM(amount) OVER ()                      AS grand_total
FROM orders
WHERE status = 'completed';
-- Every completed order row is returned, with user-level and overall totals beside it

ORDER BY Inside OVER and the Default Frame

Adding ORDER BY inside OVER() changes the default frame from the whole partition to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a cumulative window. This makes SUM() a running total automatically. Use ROWS BETWEEN for precise row-counted frames instead of RANGE to avoid tie-related surprises.

SQL — ORDER BY in OVER and frame clause

-- Default frame with ORDER BY = running total (RANGE UNBOUNDED PRECEDING to CURRENT ROW)
SELECT
    id,
    created_at::date      AS order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at          -- default frame kicks in: cumulative sum
    ) AS running_total
FROM orders;

-- Explicit frame: last 3 rows inclusive of current (ROWS, not RANGE)
SELECT
    id,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

Execution Order and Filtering Window Results

Window functions run after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT. You cannot reference a window function alias in WHERE. Wrap the query in a CTE or subquery to filter on a computed window value.

SQL — filtering on window result via CTE

-- WRONG: cannot filter on window result in WHERE
SELECT id, user_id, amount,
       RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
FROM orders
WHERE rnk = 1;   -- ERROR: column "rnk" does not exist at this point

-- CORRECT: wrap in CTE or subquery
WITH ranked AS (
    SELECT id, user_id, amount,
           RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
    FROM orders
)
SELECT * FROM ranked WHERE rnk = 1;

Key Points to Remember

1OVER() marks a function as a window function; without it the same function (SUM, COUNT) is a plain aggregate.
2PARTITION BY groups rows for the window computation but does not collapse rows in the output — all rows are preserved.
3ORDER BY inside OVER changes the default frame to a cumulative (running) window from unbounded preceding to the current row.
4ROWS BETWEEN gives precise row-count frames; RANGE BETWEEN works on value ranges and can behave unexpectedly with ties.
5Window functions execute after WHERE/GROUP BY/HAVING; filter on their results using a CTE or derived table.
6An empty OVER() with no PARTITION BY or ORDER BY computes the function over the entire result set.

Interview Questions

What is the difference between PARTITION BY and GROUP BY?

MediumAmazon

Why can you not use a window function result in a WHERE clause?

MediumGoogle

What is the default frame when ORDER BY is present inside OVER()?

HardMicrosoft

Write a query showing each order alongside the total orders placed by that user using a window function.

MediumUber

Ask Aria about Window Function Basics

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

HAVING vs WHERE

ROW_NUMBER, RANK & DENSE_RANK