You want a result set showing each employee's name, salary, and their department's average salary — all in one row per employee. Why does GROUP BY fail here?
AGROUP BY doesn't support AVG for salary columns
BGROUP BY collapses rows — you'd get one row per department, losing the individual employee rows
CYou'd need to join the table to itself, which GROUP BY prohibits
DGROUP BY only works with COUNT, not AVG
GROUP BY aggregates rows into groups, producing one output row per group. If you GROUP BY department, you get one row per department with the average salary — the individual employee records disappear. Window functions solve exactly this problem: `AVG(salary) OVER (PARTITION BY department)` computes the department average while preserving every employee's individual row in the output. Options A, C, and D are simply false about how GROUP BY and AVG work.
Question 2 Multiple Choice
What effect does adding ORDER BY inside an OVER clause have on a window function like SUM()?
AIt changes which rows are included in the result set
BIt sorts the final output just like ORDER BY at the end of the query
CIt changes the calculation from a total across the full partition to a running cumulative total
DIt has no effect — ORDER BY only matters outside the OVER clause
ORDER BY inside OVER defines a frame within the partition — by default, all rows from the start of the partition up to and including the current row. This turns SUM(amount) into a running total that accumulates as you move through the ordered rows. Without ORDER BY, the full partition is the frame and every row gets the same total. The ORDER BY here is conceptually distinct from the query-level ORDER BY, which controls output sorting, not calculation frames.
Question 3 True / False
You can use a window function result directly in a WHERE clause to filter rows — for example, WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) = 1.
TTrue
FFalse
Answer: False
Window functions are evaluated after WHERE, GROUP BY, and HAVING — so the window function result doesn't exist yet when the WHERE clause is processed. Filtering on a window function result requires wrapping the query in a subquery or CTE, then filtering in the outer query: `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees) t WHERE rn = 1`. This evaluation order is one of the most common points of confusion with window functions.
Question 4 True / False
PARTITION BY inside an OVER clause groups rows for the window calculation in a conceptually similar way to GROUP BY, but without collapsing those rows into a single output row.
TTrue
FFalse
Answer: True
This is exactly right. PARTITION BY divides the rows into groups (partitions) and the window function operates independently within each partition — exactly like GROUP BY defines groups for aggregation. The crucial difference is that GROUP BY reduces the result set to one row per group, while PARTITION BY keeps all input rows, each receiving a value computed from its partition. A window function with no PARTITION BY treats the entire table as one partition.
Question 5 Short Answer
Why can't you filter on a window function result in a WHERE clause, and what is the correct way to do it?
Think about your answer, then reveal below.
Model answer: Window functions are evaluated late in query processing — after WHERE, GROUP BY, and HAVING — so a window function result does not yet exist when the WHERE clause runs. To filter on a window function result, wrap the query in a subquery or CTE that computes the window function, then apply the filter in the outer query's WHERE clause. For example: SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk <= 3.
SQL has a defined logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT (including window functions) → ORDER BY → LIMIT. The WHERE clause runs before window functions are computed, so they cannot appear in WHERE. This is a fundamental architectural fact about SQL, not a bug — it means filters on window results always require a wrapping subquery or CTE.