Window Functions Introduction (OVER, PARTITION BY) in SQL
A window function computes a value for each row using a set of related rows, without collapsing those rows into a single output row. The `OVER` clause defines which rows are related to each other and in what order.
A window function gives you aggregate-style calculations — SUM, AVG, COUNT — while keeping every row in the output.
With GROUP BY, SQL collapses rows into one per group. You get the total, but the individual rows disappear. Sometimes that's what you want. But sometimes you need both: the aggregate and the individual rows together. A report that shows each order alongside the customer's total spend. A product list that shows each item next to the average price in its category. For these questions, GROUP BY collapses too much. The OVER clause is the answer.
OVER () with nothing inside it runs the calculation across every row in the result set. Every row gets the same computed value:
Every row shows its own total_amount alongside the same grand_total — the sum of all orders. No rows are removed. No groups are collapsed.
PARTITION BY inside OVER splits the calculation into groups, one per distinct value of the partition column. The function computes independently within each group, but every row still appears:
SELECT
id AS order_id,
status,
total_amount,
SUM(total_amount) OVER (PARTITION BY status) AS status_total
FROM ordersA completed order shows the total for all completed orders in status_total. A pending order shows the total for all pending orders. The partition determines which rows contribute to the calculation for each row.
ORDER BY inside OVER is different from ORDER BY at the end of a query. At the end, ORDER BY sorts the final result. Inside OVER, it changes what the function computes: an aggregate with ORDER BY inside OVER becomes a running total — the sum of all rows from the start of the partition up to and including the current row:
SELECT
id AS order_id,
created_at,
total_amount,
SUM(total_amount) OVER (ORDER BY created_at) AS running_total
FROM ordersEach row's running_total is the cumulative sum of all orders up to that date. The ORDER BY inside OVER is not sorting the output — it's defining how the window function accumulates.
The one thing that trips people up: you cannot filter on a window function result in WHERE.
Window functions are evaluated after WHERE. So WHERE running_total > 1000 doesn't work — SQL hasn't computed the window yet at that point. If you need to filter on a window result, wrap the query in a subquery or CTE and apply the filter outside:
WITH order_running AS (
SELECT id, total_amount,
SUM(total_amount) OVER (ORDER BY created_at) AS running_total
FROM orders
)
SELECT *
FROM order_running
WHERE running_total > 1000What is the difference between SUM(revenue) with GROUP BY region and SUM(revenue) OVER (PARTITION BY region)?
9 Window Functions Introduction (OVER, PARTITION BY) practice problems
Write a query to return the ID and amount of every order, plus the combined total amount across every order on each row.
Write a query to return the ID and status of every order, plus the total number of orders on each row.
Write a query to return the ID, name, category, and price of every product, plus the average price across the product's category on each row.
Write a query to return the ID, status, and amount of every order, plus the total revenue across every order in that status on each row.
Write a query to return the ID and customer ID of every order, plus the total number of orders placed by that customer on each row.
Write a query to return the ID, user ID, and event count of every session, plus the average event count across that user's sessions on each row.
Write a query to return the ID, category ID, and price of every product, plus both the minimum and maximum prices across the product's category on each row.
Write a query to return the ID, status, and amount of every order, plus the running total of amounts within the order's status group up to and including that order on each row.
Write a query to return the ID, category ID, and price of every product, plus the catalog-wide average price and the category average price on each row.
These problems are part of the Window Functions Introduction (OVER, PARTITION BY) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.