179 questions
No questions match those filters.
What's the actual difference between window functions a...
This is one of the questions in the full AI/ML interview bank. Pro unlocks all 1789 questions; Premium includes the same bank plus the highest daily Practice limit.
See plansA useful mental test: if the query answer needs one row per group, GROUP BY is right; if it needs one row per original row with group-level context attached, only a window function can do it. SELECT department, AVG(salary) FROM employees GROUP BY department collapses each department to one row — you’ve lost every individual employee. SELECT name, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees keeps every employee row and attaches their department’s average as a new column, letting you compute salary - dept_avg per employee in the same query.
The three window function families cover almost every analytical SQL pattern that shows up in interviews. Aggregate windows (SUM() OVER, AVG() OVER) handle running totals and moving averages by adding an ORDER BY and a frame clause like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a trailing 7-row window. Ranking windows (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()) handle “top N per group” and percentile-bucketing tasks. Navigation windows (LAG(), LEAD()) handle “compare this row to the previous/next row in its partition,” which is the backbone of month-over-month comparisons and session-boundary detection.
The interview signal worth stating explicitly: window functions execute logically after WHERE, GROUP BY, and HAVING but before the final ORDER BY and SELECT DISTINCT, which is why you can’t filter directly on a window function’s result in the same query’s WHERE clause — you have to wrap the query in a subquery or CTE and filter in the outer query instead.