🪟 Window Functions are advanced SQL functions that perform calculations across a set of rows related to the current row without grouping the rows into a single result. Unlike aggregate functions used with GROUP BY, window functions return a value for every row while still allowing access to the surrounding rows.

📖 What are Window Functions?

A window function operates on a window (or partition) of rows defined by the OVER clause. Each row is processed individually, but the calculation considers other rows within the same partition or ordered sequence.

🎯 Why Use Window Functions?

Window functions make analytical queries simpler and more efficient without requiring complex subqueries or self-joins.

📋 Sample Table

📝 Basic Syntax

SELECT
    column_name,
    window_function() OVER (
        PARTITION BY column_name
        ORDER BY column_name
    ) AS result
FROM table_name;

🪟 Understanding the OVER Clause

The OVER clause defines the window used by the function.

💡 Example: Ranking Employees

SELECT
    EmployeeName,
    Department,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS RankNumber
FROM Employees;

Each department receives its own ranking based on salary, while every employee remains in the result set.

💡 Example: Running Total

SELECT
    EmployeeName,
    Salary,
    SUM(Salary) OVER (
        ORDER BY EmployeeID
    ) AS RunningTotal
FROM Employees;

The running total increases as each employee row is processed.

💡 Example: Department Average Salary

SELECT
    EmployeeName,
    Department,
    Salary,
    AVG(Salary) OVER (
        PARTITION BY Department
    ) AS DepartmentAverage
FROM Employees;

Every employee row displays the average salary for its department without using GROUP BY.

📊 Example Output

📚 Common Window Functions

⚖️ Window Functions vs GROUP BY

💼 Real-World Applications

🗄️ Database Compatibility

⚠️ Common Mistakes

⚠️ Best Practices

🚀 Key Points to Remember