SQL Window Functions

What are Window Functions?

Window functions perform calculations across rows without grouping them.

They keep individual rows while adding computed values.

Basic Syntax

function() OVER (window_definition)

OVER()

SELECT name, salary,
       AVG(salary) OVER () AS avg_salary
FROM employees;

PARTITION BY

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

ORDER BY in Window

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

ROW_NUMBER()

SELECT name,
       ROW_NUMBER() OVER (ORDER BY salary DESC)
FROM employees;

RANK()

SELECT name,
       RANK() OVER (ORDER BY salary DESC)
FROM employees;

DENSE_RANK()

SELECT name,
       DENSE_RANK() OVER (ORDER BY salary DESC)
FROM employees;

Running Total

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

Full Example

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Why Window Functions are Important

• Perform advanced analytics
• Keep row-level detail
• Used in rankings and trends
• Common in real-world SQL

Common Mistakes

• Forgetting OVER()
• Confusing GROUP BY with window functions
• Not using PARTITION BY

Practice

Rank rows within groups using a window function.

SELECT column_name,
       ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic