Window functions — Advanced SQL: windows, views and CASE

The problem they solve

GROUP BY collapses many rows into one per group: you lose the detail. Window functions compute an aggregate value while keeping all the rows. Each row sees a "window" of related rows and gets its result.

The key piece is the OVER (...) clause:

SELECT
  name,
  category,
  price,
  AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;

Here each row keeps its name and price, and also shows the average of its category. With GROUP BY category you would only get one row per category.

PARTITION BY and ORDER BY inside OVER

Inside OVER you can split and order the window:

PARTITION BY col → splits the rows into independent groups. The function restarts in each group. It is like a "GROUP BY that does not collapse".
ORDER BY col → orders the rows within the window. It is essential for rankings and running totals, because they define "the order" or "the previous one".

Numbering and ranking

SELECT name, category, price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS pos,
  RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rk
FROM products;

Function	What it does on ties
`ROW_NUMBER()`	Numbers 1, 2, 3... without repeating (breaks ties arbitrarily).
`RANK()`	Gives the same number to ties and skips the next ones (1, 1, 3).
`DENSE_RANK()`	Like `RANK()` but without gaps (1, 1, 2).

Running totals

Combining an aggregate with OVER (ORDER BY ...) gives you a running total: each row sums from the first one up to itself.

SELECT day, amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

Window functions are computed after WHERE/GROUP BY, which is why you cannot use them in a WHERE. If you need to filter by their result, wrap the query in a subquery or a CTE.

Examples

Price ranking within each category

SELECT name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
FROM products
ORDER BY category, rk;

Running total of sales by record order

SELECT id, day, amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales
ORDER BY id;