SQL Window Functions

A window function calculates across a set of related rows but, unlike GROUP BY, keeps every row in the result. You can show each order next to the customer's total, each product's share of its category, or a running total, all without losing detail. Window functions come up in almost every data analyst interview.

Syntax

FUNCTION(column) OVER (
  PARTITION BY group_column   -- optional: restart per group
  ORDER BY sort_column        -- optional: order within the window
)

• OVER () turns an ordinary function into a window function.
• PARTITION BY splits rows into groups (windows). Without it, the window is all rows.
• ORDER BY inside OVER sets the order within each window (needed for ranking and running totals).

GROUP BY vs window function

GROUP BY returns one row per customer:

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE customer_id IN (1, 3)
GROUP BY customer_id;

Result:

(2 rows)

A window function keeps every order and adds the customer's count to each:

SELECT order_id,
       customer_id,
       order_date,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders
WHERE customer_id IN (1, 3)
ORDER BY customer_id, order_date;

Result:

(7 rows)

Examples

1. Grand total on every row

OVER () with nothing inside uses all rows:

SELECT product_name,
       stock_qty,
       SUM(stock_qty) OVER () AS total_stock,
       ROUND(stock_qty * 100.0 / SUM(stock_qty) OVER (), 1) AS pct_of_stock
FROM products
ORDER BY stock_qty DESC
LIMIT 5;

Result:

(5 rows)

2. Per-group totals with PARTITION BY

Each product's price compared with its category average:

SELECT p.product_name,
       c.category_name,
       p.price,
       ROUND(AVG(p.price) OVER (PARTITION BY p.category_id), 0) AS category_avg,
       ROUND(p.price - AVG(p.price) OVER (PARTITION BY p.category_id), 0) AS vs_avg
FROM products p
JOIN categories c ON c.category_id = p.category_id
WHERE p.category_id IN (1, 3)
ORDER BY c.category_name, p.price DESC;

Result:

(6 rows)

This replaces the correlated subquery from the previous module with something shorter and faster.

3. Share of category revenue

WITH product_revenue AS (
  SELECT p.product_name,
         p.category_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_name, p.category_id
)
SELECT product_name,
       category_id,
       ROUND(revenue, 0) AS revenue,
       ROUND(revenue * 100 / SUM(revenue) OVER (PARTITION BY category_id), 1) AS pct_of_category
FROM product_revenue
WHERE category_id IN (2, 3)
ORDER BY category_id, pct_of_category DESC;

Result:

(6 rows)

Notice the pattern: aggregate with GROUP BY in a CTE, then apply window functions on top.

4. ORDER BY inside OVER: a running count

With ORDER BY, the window grows row by row:

SELECT order_id,
       order_date,
       COUNT(*) OVER (ORDER BY order_date) AS orders_to_date
FROM orders
ORDER BY order_date
LIMIT 6;

Result:

(6 rows)

Running totals get their own lesson: running totals and moving averages.

5. Filtering on a window result

Window functions run after WHERE, so filter in an outer query:

WITH priced AS (
  SELECT product_name,
         category_id,
         price,
         AVG(price) OVER (PARTITION BY category_id) AS category_avg
  FROM products
)
SELECT product_name, category_id, price, ROUND(category_avg, 0) AS category_avg
FROM priced
WHERE price > category_avg
ORDER BY category_id;

Result:

(7 rows)

The main window functions

Common mistakes

Using a window function in WHERE:

SELECT product_name
FROM products
WHERE price > AVG(price) OVER (PARTITION BY category_id);

Error: window functions are not allowed in WHERE

• Expecting fewer rows. Window functions never remove rows; use GROUP BY if you want one row per group.
• Forgetting that ORDER BY changes the frame. SUM(x) OVER (ORDER BY d) is a running total, not the group total.

Practice question

For each employee, show their salary, their department's average salary, and the difference.

SELECT employee_name,
       department,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS dept_avg,
       ROUND(salary - AVG(salary) OVER (PARTITION BY department), 0) AS diff
FROM employees
ORDER BY department, salary DESC
LIMIT 6;

Key takeaways

• Window functions calculate across related rows without collapsing them.
• OVER () = all rows; PARTITION BY = per group; ORDER BY inside = ordered/cumulative.
• They're calculated after WHERE/GROUP BY, so filter on them in an outer query.
• Aggregate in a CTE first, then apply window functions for shares and comparisons.