Confusing Window Functions with GROUP BY

-- Trying to get individual rows with GROUP BY

SELECT employee_name, department, salary, AVG(salary)

FROM employees

GROUP BY department;

-- ERROR: employee_name not in GROUP BY

-- Window function preserves all rows

SELECT employee_name, department, salary,

AVG(salary) OVER(PARTITION BY department) as dept_avg

FROM employees;

-- SUCCESS: All rows preserved with department averages

GROUP BY creates summary rows (one per group), while window functions keep all original rows and add calculated columns. Use GROUP BY when you want summaries, window functions when you want details plus context.

If you need to see individual rows alongside aggregated data, use window functions

High

GROUP BY collapses rows and requires all non-aggregate columns in GROUP BY clause

Forgetting the OVER Clause

-- Missing OVER clause

SELECT name, salary, AVG(salary) as avg_sal

FROM employees;

-- ERROR: Must use GROUP BY or window function

-- Proper window function syntax

SELECT name, salary, AVG(salary) OVER() as avg_sal

FROM employees;

-- SUCCESS: OVER() makes it a window function

Every window function must have an OVER clause. Empty OVER() means "calculate across entire result set". This is what transforms an aggregate function into a window function.

Always include OVER() - even if empty - to make aggregate functions work as window functions

Critical

Aggregate functions without GROUP BY require OVER clause to become window functions

Not Indexing PARTITION BY and ORDER BY Columns

-- No indexes on window function columns

SELECT name, department, salary,

RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank

FROM employees;

-- SLOW: Full table scan for each partition

-- Create appropriate indexes first

CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);

SELECT name, department, salary,

RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank

FROM employees;

-- FAST: Index supports both PARTITION BY and ORDER BY