Mastering SQL Window Functions

Window functions are a game-changer for analytical queries. They let you perform calculations across a set of rows related to the current row, without collapsing those rows into a single output like GROUP BY does. This means you can calculate running totals, rankings, or moving averages, all while keeping the detail of every row.

The Problem: GROUP BY Limitations

Imagine you need a report showing each employee's salary alongside the average salary for their department. A classic GROUP BY approach would require a self-join or a subquery, making the query complex and hard to read.

-- ❌ Complex approach with subquery
SELECT 
    e.depname, 
    e.empno, 
    e.salary,
    (SELECT AVG(salary) 
     FROM empsalary d 
     WHERE d.depname = e.depname) AS dept_avg
FROM empsalary e;

This works, but it's inefficient—the subquery executes for every row, and the query becomes harder to maintain.

The Solution: Window Functions

With a window function, this is elegantly handled in a single, efficient query.

-- ✅ Clean approach with window function
SELECT 
    depname, 
    empno, 
    salary, 
    AVG(salary) OVER (PARTITION BY depname) AS dept_avg
FROM empsalary;

This immediately provides a valuable insight: a developer can see at a glance exactly how their salary compares to the team average. The PARTITION BY clause creates a logical "department" window, and the AVG function is calculated across that window for each row.

Common Window Functions

1. ROW_NUMBER() – Ranking with Unique Numbers

Assigns a unique sequential number to each row within a partition, starting from 1. Perfect for deduplication or pagination.

SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

2. RANK() and DENSE_RANK() – Handling Ties

• RANK(): Assigns the same rank to ties, but skips subsequent ranks (1, 2, 2, 4)
• DENSE_RANK(): Assigns the same rank to ties without skipping (1, 2, 2, 3)

SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank
FROM products;

3. LAG() and LEAD() – Accessing Previous and Next Rows

Access data from previous or next rows without self-joins. Perfect for comparing current values with historical ones.

SELECT 
    year,
    sales,
    LAG(sales, 1, 0) OVER (ORDER BY year) AS previous_year_sales,
    sales - LAG(sales, 1, 0) OVER (ORDER BY year) AS year_over_year_growth
FROM annual_sales;

4. Running Totals with SUM() OVER()

Calculate running totals without using complex self-joins or cursors.

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

Real-World Example: Employee Performance Dashboard

Let's build a comprehensive employee dashboard using multiple window functions:

SELECT 
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_percentile
FROM employees
ORDER BY department, dept_rank;

Performance Considerations

Conclusion