SQL Quiz: Windowed Aggregates
10 questions · exam conditions
0:00
Windowed AggregatesQuestion 1 of 10

A transactions table contains these rows: C1/paid/40, C1/refund/10, C1/paid/20, and C2/paid/30.

A query includes this expression:

SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) OVER (PARTITION BY customer_id) AS paid_total

What value of paid_total appears on C1's refund row?

The refund row shows a paid total of 0.
The refund row shows a paid total of 50.
The refund row shows a paid total of 60.
The refund row shows a paid total of 70.
← Back to quizzes

SQL Quiz

SQL Quiz: Windowed Aggregates

Practice Windowed Aggregates in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Windowed Aggregates, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A transactions table contains these rows: C1/paid/40, C1/refund/10, C1/paid/20, and C2/paid/30.

A query includes this expression:

SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) OVER (PARTITION BY customer_id) AS paid_total

What value of paid_total appears on C1's refund row?

  1. The refund row shows a paid total of 0.
  2. The refund row shows a paid total of 50.
  3. The refund row shows a paid total of 60. (correct answer)
  4. The refund row shows a paid total of 70.
Explanation: When you see a window function with OVER (PARTITION BY ...), remember that the window aggregation operates across the entire partition, not just the current row. The CASE expression inside the SUM filters which values contribute to the aggregate, but the result is still broadcast to every row in the partition. For customer C1, the partition contains three rows: a paid/40, a refund/10, and a paid/20. The CASE expression assigns amount when status = 'paid' and 0 otherwise, so the values summed are 40, 0, and 20. That gives 40+0+20=6040 + 0 + 20 = 60. Crucially, this value of 60 appears on every row in C1's partition — including the refund row — because window functions attach the partition-level result to each individual row. That makes C is correct. A reflects the most common misconception: thinking the refund row "contributes 0" so it also receives 0. The CASE only controls what each row adds to the sum; it doesn't limit which rows display the result. B (50) has no basis in the data — it doesn't correspond to any meaningful combination of C1's amounts, making it a straightforward distractor. D (70) would result from mistakenly including the refund amount (40 + 10 + 20 = 70), ignoring that the CASE correctly zeroes out non-paid rows before summing. Study tip: Always separate two ideas mentally — "what does this row contribute to the window aggregate?" vs. "what value does this row display?" Those are independent questions in window function logic.

Question 2

An employees table has three rows in department A with salaries 40, 60, and 100.

Consider this query:

SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees WHERE salary >= 60;

What value of dept_avg is shown on the department A rows that remain?

  1. dept_avg is approximately 66.67.
  2. dept_avg is exactly 80. (correct answer)
  3. dept_avg is exactly 60.
  4. dept_avg is exactly 100.
Explanation: When working with window functions like AVG() OVER(...), you must pay close attention to when filtering happens relative to when the window is calculated. The WHERE clause runs before window functions are evaluated — so the window function only "sees" the rows that survive the filter. In this query, the WHERE salary >= 60 clause eliminates the employee with salary 40, leaving only the rows with salaries 60 and 100 in department A. The AVG(salary) OVER (PARTITION BY department_id) then computes the average across those remaining rows: 60+1002=80\frac{60 + 100}{2} = 80 That makes B correct — dept_avg is exactly 80 for all department A rows in the result set. A is wrong because 66.67 is the average of all three original salaries — 40+60+1003≈66.67\frac{40 + 60 + 100}{3} \approx 66.67 — a common trap if you forget that WHERE filters rows before the window function runs. C is wrong because 60 would imply only the row with salary 60 was counted, which misunderstands how PARTITION BY aggregates across all rows in the partition, not just the current row. D is wrong for the same reason — 100 would only result if the partition somehow contained only that single row. Study tip: Always trace the execution order — WHERE → window function evaluation → SELECT output. If you want a window function to see all rows but still filter the final output, you'd need a subquery or CTE with WHERE applied afterward.

Question 3

A report must return individual employees whose salary is greater than the average salary of their own department. The result must retain one row per qualifying employee.

Which query correctly uses a partitioned window aggregate to produce the report under standard SQL query-processing rules?

  1. SELECT employee_id, salary FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);
  2. SELECT employee_id, salary FROM (SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees) e WHERE salary > dept_avg; (correct answer)
  3. SELECT employee_id, salary FROM employees GROUP BY department_id, employee_id, salary HAVING salary > AVG(salary);
  4. SELECT employee_id, salary FROM (SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY employee_id) AS dept_avg FROM employees) e WHERE salary > dept_avg;
Explanation: When working with window functions in SQL, the critical rule to remember is that window functions are evaluated after the WHERE clause but before the final SELECT output. This means you cannot reference a window function expression directly inside a WHERE clause — you must first compute it in a subquery or CTE, then filter on it. Option B is correct because it wraps the window function in a derived table (subquery), computing AVG(salary) OVER (PARTITION BY department_id) as dept_avg for every row. The outer query can then safely filter with WHERE salary > dept_avg, comparing each employee's salary against their own department's average. This follows SQL's logical processing order correctly. Option A is the most tempting trap — it looks clean and readable, but it places the window function directly in the WHERE clause. Standard SQL forbids this because WHERE is evaluated before window functions are computed, so the engine has nothing to compare against yet. Most databases will throw an error here. Option C attempts to use GROUP BY with HAVING to solve this, but it misunderstands how grouping works. Grouping by department_id, employee_id, salary makes each row its own group, so AVG(salary) within HAVING just equals that single employee's salary — the comparison becomes meaningless and never filters correctly. Option D uses PARTITION BY employee_id instead of PARTITION BY department_id. This partitions by individual employee, meaning the average computed is that employee's own salary — the condition salary > dept_avg can never be true, returning no rows. As a study tip: whenever you see a window function being used for filtering, your first instinct should be to wrap it in a subquery — window functions belong in SELECT, not WHERE.

Question 4

A usage_log table contains four rows: tenant T1/category A/units 10, tenant T1/category A/units 15, tenant T1/category B/units 20, and tenant T2/category A/units 7.

The query calculates:

SUM(units) OVER (PARTITION BY tenant_id, category)

What value is returned for the T1/category A row whose units value is 15?

  1. The windowed total is 15 units.
  2. The windowed total is 25 units. (correct answer)
  3. The windowed total is 32 units.
  4. The windowed total is 45 units.
Explanation: Whenever you see OVER (PARTITION BY ...) in SQL, your job is to identify which rows belong to the same partition as the row in question — because the window function operates only within that group. Here, the partition is defined by both tenant_id and category together. Scan the table for rows where both columns match the target row (T1, category A): you find units = 10 and units = 15. Those two rows — and only those two — form the partition. So the window sum is 10+15=2510 + 15 = 25, which confirms B is correct. A (15 units) reflects the mistake of treating SUM like a scalar value that simply returns the current row's own units. Window functions aggregate across the partition, not just the current row — unlike a plain SELECT units, which would return 15. C (32 units) likely comes from summing all three T1 rows (10 + 15 + 20 = 45... actually 45), or perhaps mixing in T2's 7 with the two category A rows (10 + 15 + 7 = 32). This is the trap of partitioning only by tenant_id and ignoring category, or accidentally including T2's data. D (45 units) comes from summing all four rows (10 + 15 + 20 + 7 = 52... or just the three T1 rows: 10 + 15 + 20 = 45), which would be the result if there were no PARTITION BY clause at all — a global SUM. A reliable study tip: when evaluating a window function, always list the qualifying rows first, then apply the aggregate. Never assume the partition boundary from a single column when multiple columns are specified.

Question 5

An orders table contains these rows: order 101 belongs to customer C1, has status open, and amount 30; order 102 belongs to C1, has status canceled, and amount 50; order 103 belongs to C1, has status open, and amount 20; order 104 belongs to C2, has status open, and amount 40.

The following query is executed:

SELECT order_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS order_total FROM orders WHERE status = 'open';

What value of order_total is returned for order 103?

  1. The row shows order_total = 50. (correct answer)
  2. The row shows order_total = 100.
  3. The row shows order_total = 20.
  4. The row shows order_total = 90.
Explanation: When you see a window function like SUM(...) OVER (PARTITION BY ...), the key question to ask is: what rows are actually in the window? The answer depends on two things working together — the WHERE clause and the PARTITION BY clause. Here's the critical insight: the WHERE clause filters rows before the window function runs. So WHERE status = 'open' removes order 102 (canceled) from the dataset entirely. The window function never sees it. After filtering, the remaining rows are orders 101 (amount 30), 103 (amount 20), and 104 (amount 40). Now PARTITION BY customer_id groups these into two partitions: customer C1 gets orders 101 and 103, and customer C2 gets order 104. For order 103, the window sums only C1's open orders: 30+20=5030 + 20 = 50. So order_total = 50, confirming A is correct. B (100) would be the sum of all amounts in the table (30 + 50 + 20 + 40), ignoring both the WHERE filter and the PARTITION BY grouping — a double mistake. C (20) is just order 103's own amount, confusing a plain column value with what the window function computes. D (90) likely comes from summing all open orders regardless of partition (30 + 20 + 40), forgetting that PARTITION BY customer_id keeps C1 and C2 separate. A reliable study tip: always mentally apply WHERE first to shrink your dataset, then apply the window function to whatever rows remain. These two steps are sequential, not simultaneous.

Question 6

A sales table contains three rows: region East with amounts 10 and 20, and region West with amount 5.

What result is produced by this query?

SELECT DISTINCT region, SUM(amount) OVER (PARTITION BY region) AS region_total FROM sales;

  1. Two rows: East with 30, and West with 5 (correct answer)
  2. Three rows: East with 10, East with 20, and West with 5
  3. Two rows: East with 35, and West with 35
  4. Three rows: East with 30, East with 30, and West with 5
Explanation: When you see a window function combined with DISTINCT, you need to think carefully about the order of operations in SQL. The window function (SUM(...) OVER) runs first, computing values across the full dataset, and then DISTINCT collapses duplicate rows. Here's what happens step by step. The PARTITION BY region clause makes SUM(amount) calculate separately for each region. Every East row receives the value 30 (10 + 20), and the West row receives 5. So before DISTINCT, the result set looks like three rows: (East, 30), (East, 30), (West, 5). Then DISTINCT removes the duplicate (East, 30) row, leaving exactly two rows: East with 30 and West with 5 — confirming that A is correct. Choice B describes what you'd get without DISTINCT at all — three raw rows where each amount is still separate. That ignores the effect of DISTINCT entirely. Choice C suggests both regions share a grand total of 35, which would happen with SUM(amount) OVER () (no partition), not PARTITION BY region — a classic mix-up between partitioned and unpartitioned window functions. Choice D is the most tempting trap: it correctly shows that both East rows each get 30, but it forgets that DISTINCT then merges those two identical rows into one. A good rule of thumb: always trace window functions in two passes — first, what value does each row receive? Second, does DISTINCT collapse any resulting duplicates? That two-pass thinking will save you on any question mixing DISTINCT with OVER.

Question 7

A sales table has three rows: one row with region = NULL and amount 10, another row with region = NULL and amount 15, and one row with region = 'East' and amount 20.

The query calculates SUM(amount) OVER (PARTITION BY region) AS region_total. What region_total appears on each row whose region is NULL?

  1. Each such row receives a total of NULL.
  2. Each such row receives a total of 10.
  3. Each such row receives a total of 15.
  4. Each such row receives a total of 25. (correct answer)
Explanation: When you see window functions with PARTITION BY, think of them as creating invisible buckets — rows are grouped by the partition key, and the function runs independently within each bucket. The critical concept here is how SQL handles NULL as a partition value. NULL in a PARTITION BY clause does not mean "no partition" or "exclude these rows." Instead, SQL treats all NULL values as belonging to the same partition together. So both rows where region = NULL are grouped into one bucket, their amounts (10 and 15) are summed, and every row in that bucket receives 25 as its region_total. The 'East' row forms its own separate bucket, receiving 20. D is correct because 10+15=2510 + 15 = 25, and both NULL-region rows receive that shared total. A reflects a common misconception: that because NULL represents an unknown, any calculation involving it must return NULL. That rule applies to arithmetic expressions (like NULL + 10), but PARTITION BY groups rows by identity, not by computation. NULLs are grouped together, not propagated. B would only be correct if each NULL row were partitioned separately from the other, receiving only its own amount. That's not how it works — NULLs consolidate. C has the same flaw as B, suggesting only the second row's value (15) is used rather than the combined total. Study tip: Remember the rule — in PARTITION BY, NULL = NULL for grouping purposes. This is the opposite of how NULL behaves in WHERE or JOIN conditions, where NULL <> NULL.

Question 8

Department X has three employee rows. Their salary values are NULL, 50, and 70.

For every row in department X, a query returns these expressions:

COUNT(salary) OVER (PARTITION BY department_id)

COUNT(*) OVER (PARTITION BY department_id)

AVG(salary) OVER (PARTITION BY department_id)

Which tuple of values is returned?

  1. (2, 3, 60) for every department X row (correct answer)
  2. (3, 3, 40) for every department X row
  3. (2, 2, 60) for every department X row
  4. (3, 3, 60) for every department X row
Explanation: Whenever you see window functions like COUNT and AVG with OVER (PARTITION BY ...), the key question to ask is: how does each function handle NULL values? Here's the rule: COUNT(column_name) counts only non-NULL values, while COUNT(*) counts all rows, including those with NULLs. For AVG(column_name), SQL ignores NULLs in both the sum and the denominator — it does not treat NULL as zero. With salaries of NULL, 50, and 70 in department X:
  • COUNT(salary) skips the NULL and counts 2 non-NULL values → 2
  • COUNT(*) counts all three rows regardless → 3
  • AVG(salary) computes (50+70)÷2=60(50 + 70) \div 2 = 60, dividing by 2 (not 3) because the NULL is excluded → 60
This makes answer A — (2, 3, 60) for every department X row correct. Because these are window functions, the same partitioned result is returned for all three rows, including the row where salary is NULL. Answer B is wrong because COUNT(salary) would never equal 3 here — that ignores NULL exclusion. Answer D makes the same mistake with COUNT(salary) = 3. Answer C incorrectly sets COUNT(*) = 2, as if NULL caused that row to disappear entirely, which is only true for COUNT(column), not COUNT(*). Study tip: Memorize this trio — COUNT(col) excludes NULLs, COUNT(*) never does, and AVG silently drops NULLs from both the numerator and denominator. This distinction appears frequently in SQL window function questions.

Question 9

Customer C1 has order 1 with amount 100 and order 2 with amount 50. An order_items table contains two item rows for order 1 and one item row for order 2.

The tables are joined using this query:

SELECT o.order_id, i.item_id, SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total FROM orders o JOIN order_items i ON i.order_id = o.order_id;

What customer_total is displayed on the joined row for order 2?

  1. The displayed customer total is 50.
  2. The displayed customer total is 150.
  3. The displayed customer total is 200.
  4. The displayed customer total is 250. (correct answer)
Explanation: When working with window functions alongside joins, you need to think carefully about when the window function runs and what data it sees — specifically, it operates on the result set after the join, not on the original base tables. Here's what happens step by step. The JOIN between orders and order_items produces these rows before any aggregation:
  • Order 1, Item A (amount = 100)
  • Order 1, Item B (amount = 100)
  • Order 2, Item C (amount = 50)
Notice that order 1 appears twice because it has two item rows. Now the window function SUM(o.amount) OVER (PARTITION BY o.customer_id) sums all amount values within customer C1's partition. That partition contains 100 + 100 + 50 = 250. Every row in that partition — including the single row for order 2 — displays 250 as customer_total. That makes D correct. Answer A (50) is wrong because it only considers order 2's amount in isolation, ignoring that the window spans all of C1's rows. Answer B (150) reflects a naive sum of the original order amounts (100 + 50), as if the join hadn't duplicated order 1's rows. Answer C (200) might come from doubling order 2's amount or some other miscounting, but it has no grounding in the actual row set. The key strategy here: always mentally "flatten" your JOIN result into individual rows first, then apply the window function to that expanded set. Joins can silently duplicate rows, inflating window function results in ways that feel counterintuitive until you trace through the data row by row.

Question 10

A sales table contains these rows: East/department A/amount 10, East/department A/amount 15, East/department B/amount 20, and West/department A/amount 7.

The following query aggregates departments and then calculates a regional window total:

SELECT region, department, SUM(amount) AS dept_sales, SUM(SUM(amount)) OVER (PARTITION BY region) AS region_sales FROM sales GROUP BY region, department;

Which values are returned for the East/department A row?

  1. dept_sales = 45 and region_sales = 45
  2. dept_sales = 25 and region_sales = 25
  3. dept_sales = 25 and region_sales = 45 (correct answer)
  4. dept_sales = 25 and region_sales = 52
Explanation: When a query combines GROUP BY with a window function, you need to think in two distinct phases. First, the GROUP BY collapses rows and applies aggregate functions. Then, window functions operate on the result of that aggregation, not on the original raw rows. Here's how to trace through this query. The GROUP BY region, department produces three intermediate rows: East/A with SUM(amount) = 25 (10 + 15), East/B with SUM(amount) = 20, and West/A with SUM(amount) = 7. So for the East/A row, dept_sales = 25. That eliminates choice A immediately. Now the window function SUM(SUM(amount)) OVER (PARTITION BY region) runs on those aggregated rows. The inner SUM(amount) is already resolved to each group's dept_sales value. The outer SUM(...) then totals those values within each partition. For the East partition, it adds the two East groups: 25+20=4525 + 20 = 45. So region_sales = 45 for both East rows, confirming C is correct. Choice B is wrong because it assumes the window function only sees the current group's value (25), not the sum across all East groups. Choice D appears to sum all four original rows (10 + 15 + 20 + 7 = 52), which would be the result without a PARTITION BY region clause and without proper grouping — it ignores that the window is partitioned by region. Study tip: When you see nested aggregates like SUM(SUM(...)) with a window clause, always resolve the inner aggregate first via GROUP BY, then apply the outer window function to those results — they operate at different logical phases.