Window Functions Intro — The OVER() Clause

Here is the problem with GROUP BY: it collapses rows. You ask for COUNT per customer, you get one row per customer. The individual orders disappear. But what if you want to see every order AND its rank within that customer? What if you want each row to show the running total? You need something that calculates across rows without collapsing them. That is window functions.

The "Window" Analogy

Imagine you are in a train. Each window shows a different section of the landscape. A window function is like looking through a specific window — you see a group of related rows and calculate something about them. But you still see every individual row. The window defines which rows to look at. The function defines what to calculate.

GROUP BY vs Window Functions

The OVER() Clause — Foundation of Every Window Function

Every window function uses the OVER() clause. This is what makes it a window function instead of a regular aggregate. OVER() defines the "window" — which rows to include in the calculation. An empty OVER() means "all rows in the result set."

-- Regular aggregate: one row total
SELECT SUM(total_amount) AS total_revenue
FROM orders;
-- Result: 1 row with the grand total

-- Window function: every row with the total alongside
SELECT
  order_id,
  customer_id,
  total_amount,
  SUM(total_amount) OVER() AS grand_total
FROM orders;
-- Result: every order row, PLUS a column showing the grand total
-- Every row shows the same grand_total value

-- Now you can do math: what percentage of total revenue is each order?
SELECT
  order_id,
  customer_id,
  total_amount,
  SUM(total_amount) OVER() AS grand_total,
  ROUND(total_amount * 100.0 / SUM(total_amount) OVER(), 2) AS pct_of_total
FROM orders
ORDER BY pct_of_total DESC;

OVER() with ORDER BY — Running Calculations

Add ORDER BY inside OVER() and the calculation becomes cumulative. Instead of the grand total, you get a running total — each row adds to the previous sum.

-- Running total of order amounts (ordered by date)
SELECT
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER(ORDER BY order_date) AS running_total
FROM orders;

-- Result:
-- order_id | order_date | total_amount | running_total
-- 1        | 2024-01-01 | 500          | 500
-- 2        | 2024-01-02 | 300          | 800
-- 3        | 2024-01-03 | 700          | 1500
-- 4        | 2024-01-04 | 200          | 1700
-- Each row shows the sum of all amounts up to that row