OVER & PARTITION BY

Every window function needs an OVER clause — it's what turns a plain function call into a window function and tells the database exactly what "window" of rows to look at. This lesson focuses on the two most important pieces you put inside it: PARTITION BY and ORDER BY.

Note

This lesson builds directly on the previous one. If "a window function computes a value without collapsing rows" doesn't feel solid yet, revisit Window Functions Introduction first.

Sample Data

SQL

SELECT * FROM sales ORDER BY region, sale_date;


id | sale_date  | salesperson | region | amount
---|------------|-------------|--------|-------
1  | 2024-01-01 | Amir        | East   | 400.00
2  | 2024-01-02 | Amir        | East   | 250.00
3  | 2024-01-03 | Amir        | East   | 600.00
7  | 2024-01-01 | Chen        | East   | 500.00
4  | 2024-01-01 | Bilal       | West   | 300.00
5  | 2024-01-02 | Bilal       | West   | 300.00
6  | 2024-01-03 | Bilal       | West   | 150.00

The Bare OVER() Clause

The simplest possible window is the entire result set — every row is treated as one big window. Writing OVER () with nothing inside it means "compute this across all rows returned by the query."

SQL

SELECT
  salesperson,
  region,
  amount,
  SUM(amount) OVER () AS grand_total
FROM sales;


salesperson | region | amount | grand_total
------------|--------|--------|------------
Amir        | East   | 400.00 | 2500.00
Amir        | East   | 250.00 | 2500.00
Amir        | East   | 600.00 | 2500.00
Chen        | East   | 500.00 | 2500.00
Bilal       | West   | 300.00 | 2500.00
Bilal       | West   | 300.00 | 2500.00
Bilal       | West   | 150.00 | 2500.00

Every row shows the same grand_total because there is only one window — the whole table. That's rarely useful on its own; usually we want separate totals per group. That's what PARTITION BY is for.

PARTITION BY: Independent Windows per Group

PARTITION BY column splits the rows into independent groups — one window per distinct value — and the window function is computed separately within each group. This is conceptually very similar to GROUP BY, with one crucial difference: PARTITION BY does not collapse rows. Every row stays in the output; only the value being computed is scoped to its partition.

SQL

SELECT
  salesperson,
  region,
  sale_date,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY region, sale_date;


salesperson | region | sale_date  | amount | region_total
------------|--------|------------|--------|-------------
Amir        | East   | 2024-01-01 | 400.00 | 1750.00
Amir        | East   | 2024-01-02 | 250.00 | 1750.00
Amir        | East   | 2024-01-03 | 600.00 | 1750.00
Chen        | East   | 2024-01-01 | 500.00 | 1750.00
Bilal       | West   | 2024-01-01 | 300.00 | 750.00
Bilal       | West   | 2024-01-02 | 300.00 | 750.00
Bilal       | West   | 2024-01-03 | 150.00 | 750.00

East's four rows all show 1750.00 (their region's total) and West's three rows all show 750.00. The database computed two separate windows — one for East, one for West — but returned every row from the original table.

	GROUP BY	PARTITION BY (inside OVER)
Groups rows for the calculation?	Yes	Yes
Collapses rows into one per group?	Yes	No — every row is kept
Can mix detail columns with the aggregate?	No — only grouped/aggregated columns allowed	Yes — any column plus the window result
Multiple different groupings in one query?	No — one GROUP BY per query	Yes — each window function can have its own PARTITION BY

Tip

You can add several window functions to the same query, each with a different \`PARTITION BY\`. For example, show a row's region total and its salesperson total side by side in the same SELECT — something GROUP BY alone cannot do in one pass.

Adding ORDER BY Inside OVER()

So far our examples don't care about row order — a sum is a sum regardless of sequence. But many window functions are inherently order-sensitive: ranking a row depends on where it falls relative to others, and a running total depends on which rows came "before" the current one. For these, you add an ORDER BY inside the OVER() clause.

SQL

SELECT
  salesperson,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
  ) AS running_total
FROM sales
WHERE salesperson = 'Amir'
ORDER BY sale_date;


salesperson | sale_date  | amount | running_total
------------|------------|--------|--------------
Amir        | 2024-01-01 | 400.00 | 400.00
Amir        | 2024-01-02 | 250.00 | 650.00
Amir        | 2024-01-03 | 600.00 | 1250.00

Notice the difference from before: instead of every row showing the same total (1250.00), each row now shows the cumulative total up to and including that row's date. Adding ORDER BY changed the default window frame the function operates over — a topic explored fully in Running Totals & Moving Averages and Window Frame Clauses.

PARTITION BY — WHICH rows belong together (optional; omit it to treat the whole result set as one window).
ORDER BY — WHAT SEQUENCE the rows are processed in within each partition (required for ranking functions and affects running-total behavior for aggregates).
Both go inside the same OVER (...) clause, PARTITION BY first, then ORDER BY.

Warning

The `ORDER BY` inside `OVER()` is completely independent of any `ORDER BY` at the end of the query. The inner one controls how the window function computes its value; the outer one controls the final row order of the result set. You often need both, and they can sort by different columns.

What's Next

Now that you understand how OVER(), PARTITION BY, and ORDER BY define a window, the next lessons cover the functions that actually run inside that window: ROW_NUMBER, RANK, DENSE_RANK, and NTILE for ranking rows, then LAG/LEAD and FIRST_VALUE/LAST_VALUE for reaching into neighboring rows.