How to Use Window Functions in SQL with Examples

Window functions in SQL are powerful tools that allow you to perform calculations across a set of table rows related to the current row. Unlike aggregate functions that group rows into a single output, window functions keep all rows visible and calculate running totals, rankings, averages, and more without collapsing data. This tutorial is designed for beginners who want to understand and start using window functions effectively along with concepts like GROUP BY and JOINs.

Simply put, a window function performs a calculation across rows that are defined by a window or frame. The window frame specifies the rows related to the current row within a partition, which is a subset of data grouped by certain column values. This differs from regular aggregate functions because the output row count remains the same, making window functions ideal for scenarios like ranking employees by salary within departments or calculating running totals in time series data.

SELECT
  employee_id,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary,
  SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

-- Explanation:
-- RANK() assigns rank per department based on salary descending.
-- AVG() calculates average salary per department.
-- SUM() calculates running total of salaries ordered by employee_id.

To use window functions correctly, always specify the OVER() clause which defines the window. You can partition data using PARTITION BY and order it with ORDER BY inside the window. Keep in mind that the frame specification like ROWS BETWEEN or RANGE BETWEEN controls which rows are included in aggregation. Combining window functions with filtering using WHERE works differently because WHERE filters rows before window functions are applied. Instead, use HAVING or a subquery if needed. Understanding window frame boundaries and partitions will help you avoid mistakes and write efficient queries.

A common mistake is confusing window functions with group aggregate functions. Unlike GROUP BY which reduces the number of rows, window functions keep all rows. Another error is missing the OVER() clause or incorrectly ordering rows, which can lead to unexpected results. Using window functions inside WHERE clauses is invalid as SQL processes WHERE before window functions. Also, beware of performance issues on large datasets without proper indexing, and remember to check your SQL dialect since window function support details can differ.

In summary, window functions are essential for advanced SQL queries that require calculations across related rows without losing detail. By mastering PARTITION BY, ORDER BY, and frame clauses inside OVER(), alongside understanding aggregate functions and JOINs, you can write flexible queries for ranking, running totals, moving averages, and more. Practice the examples provided and experiment with your own data to deepen your understanding and enhance your SQL skills.