Day 15: Window Functions

Day 15: Window Functions

1. What are Window Functions?

    Window functions perform calculations across a set of rows related to the current row, helping analyze data without grouping.

2. Types of Window Functions

• Aggregate Functions: SUM(), AVG(), COUNT(), MIN(), MAX().
• Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
• Value Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

    Syntax

    FunctionName() OVER ( PARTITION BY ColumnName ORDER BY ColumnName )

    Examples

        a) Aggregate with PARTITION

            Calculate total salary for each department:

            SQL

 

        SELECT EmployeeID, DepartmentID, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS TotalSalary FROM Employees;

        In Department 101, if employees earn 5000, 6000, and 4000, the total salary for all rows is 15000.

        In Department 102, with only one employee earning 7000, the total salary is 7000.

    b) ROW_NUMBER

            Assign a unique number to each row based on salary:

            SQL

        SELECT EmployeeID, Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber FROM Employees;

            If salaries are 7000, 6000, 5000, employees are ranked as 1, 2, and 3 respectively.

        c) RANK and DENSE_RANK
            Rank employees based on salary:

            SQL

        SELECT EmployeeID, Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

            With salaries 7000, 6000, 6000:

            RANK: 1, 2, 2 (skips 3 for ties).

            DENSE_RANK: 1, 2, 2 (does not skip numbers for ties).

        d) LAG and LEAD

            Fetch the previous and next salaries in a sequence:

            SQL

        SELECT EmployeeID, Name, Salary, LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary FROM Employees;

            For salaries 4000, 5000, 6000:

            PreviousSalary: NULL, 4000, 5000.

            NextSalary: 5000, 6000, NULL.

    3. Key Takeaways

        PARTITION BY groups data into subsets for calculations.
        ORDER BY defines the sequence for calculations.
        Use window functions to analyze data efficiently without grouping rows.

Action Steps

1. Write a query using SUM() and PARTITION BY to calculate group totals.
2. Use ROW_NUMBER to rank rows based on any column.
3. Experiment with LAG and LEAD to fetch previous and next row values.