Mastering SQL Window Functions

Window functions are one of the most powerful features in SQL. They allow you to perform calculations across a set of table rows that are somehow related to the current row.

What Are Window Functions?

Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row — the rows retain their separate identities.

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
    RANK()       OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM learners;

LAG and LEAD

These functions let you access data from previous or next rows:

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date)  as prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) as next_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change
FROM sales;

Conclusion

Window functions are essential for any data analyst. Practice with real datasets to build your skills!