By the end of this lesson you can write an aggregate as a window function with OVER (), explain how it differs from GROUP BY, and know where in the query a window function is allowed.
Why it matters: Most analytical questions compare a row with a group it belongs to: this product against the catalogue average, this category against total revenue. Window functions answer them in one pass, keeping the detail and the context side by side.
Dataset: Wadi Market online store. Examples run in the editor on the right.
You already know how to summarise rows with GROUP BY: one row goes in for each order, one row comes out for each group. That's perfect for a summary table, and useless when you need the detail and the summary at the same time. Ask “which products are priced above the catalogue average, and by how much?” and GROUP BY gets in your way: as soon as you compute the average, the individual products are gone.
A window function solves this. It computes a value over a set of rows, called the window, and writes the answer onto every row, leaving the rows themselves untouched. The number of rows in the result stays exactly the same as without the window function.
aggregate_function(expression) OVER ()SELECT product_name,
unit_price,
ROUND(AVG(unit_price) OVER (), 2) AS avg_price
FROM products
ORDER BY unit_price DESC, product_name;All 61 products come back, one row each. AVG(unit_price) OVER () is calculated once over all 61 prices and the same average is repeated on every row, so you can read each price against it directly.
Once the average sits on the row, you can use it in arithmetic like any other column. The next query computes each product's distance from the average and its share of the summed list prices. Notice that SUM(unit_price) OVER () and COUNT(*) OVER () work exactly the same way.
SELECT product_name,
unit_price,
ROUND(unit_price - AVG(unit_price) OVER (), 2) AS diff_from_avg,
ROUND(100 * unit_price / SUM(unit_price) OVER (), 2) AS pct_of_total,
COUNT(*) OVER () AS products_listed
FROM products
ORDER BY diff_from_avg DESC, product_name;diff_from_avg is positive for products above the average and negative below it. pct_of_total divides each price by the sum of all prices, and products_listed shows 61 on every row: three different aggregates, all evaluated over the same window, none of them removing a row.
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS result
FROM staff;| name | dept | salary | result |
|---|---|---|---|
| Lina | Finance | 18000 | 1 |
| Omar | Finance | 15000 | 2 |
| Reem | Finance | 15000 | 2 |
| Ali | Sales | 21000 | 1 |
| Dana | Sales | 12000 | 2 |
Unlike GROUP BY, a window function keeps every row and adds a value computed over a set of related rows (the window). PARTITION BY dept restarts the calculation for each department. Omar and Reem tie, so both get the same rank and the next rank is skipped.
Where a window function runs
Window functions are evaluated late: after FROM, WHERE, GROUP BY and HAVING, just before ORDER BY and LIMIT. Two consequences follow, and both matter every day:
- The window only sees rows that survived WHERE. Filter out discontinued products and the average is the average of the products that are still sold. That's usually what you want, but be aware of it.
- You can't use a window function in WHERE (or in GROUP BY or HAVING), because when WHERE runs, the window values do not exist yet. To filter on one, compute it in a CTE or subquery first and filter in the outer query.
WITH priced AS (
SELECT product_name,
unit_price,
AVG(unit_price) OVER () AS avg_price
FROM products
WHERE NOT discontinued
)
SELECT product_name, unit_price, ROUND(avg_price, 2) AS avg_price
FROM priced
WHERE unit_price > avg_price
ORDER BY unit_price DESC, product_name;Inside the CTE the WHERE keeps the 56 products still on sale, so the average is computed over those 56 only. The outer query can then filter on avg_price, because by then it is an ordinary column of the CTE.
Windows over grouped results
Because the window runs after GROUP BY, it can work on the grouped rows. That gives you a neat pattern for share of total reports: aggregate first, then put a window over the aggregates. SUM(SUM(x)) OVER () looks odd the first time: the inner SUM is the group's total (from GROUP BY), and the outer SUM … OVER () adds those group totals across all groups.
SELECT channel,
COUNT(*) AS orders,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_orders
FROM orders
WHERE ordered_at >= DATE '2025-01-01' AND ordered_at < DATE '2026-01-01'
GROUP BY channel
ORDER BY orders DESC;GROUP BY produces three rows, one per channel. The window SUM(COUNT(*)) OVER () then adds the three counts together, so each channel's count can be divided by the year's total. The three percentages add up to 100.