🚀 Data Analyst Roadmap — Part 16

🧠 SQL Level 6 — Window Functions

Window functions are one of the most important SQL skills for a Data Analyst.

They allow you to perform calculations across related rows without losing the individual rows.

Instead of collapsing data like GROUP BY, window functions let you analyze each row in the context of other rows.

🔹 1. GROUP BY vs Window Functions

Suppose you have:

Employee | Department | Salary
John     | IT         | 75,000
Mike     | IT         | 90,000
Lisa     | IT         | 90,000
Sarah    | HR         | 60,000
Alice    | HR         | 70,000

With GROUP BY:

SELECT Department, AVG(Salary) AS Avg_Salary
FROM Employees
GROUP BY Department;

You get one row per department.

With a window function:

SELECT
    Employee,
    Department,
    Salary,
    AVG(Salary) OVER (PARTITION BY Department) AS Avg_Dept_Salary
FROM Employees;

You keep every employee while also seeing their department's average salary.

👉 GROUP BY reduces rows.

👉 Window functions preserve rows.

🔹 2. Understanding OVER()

Every window function uses the OVER() clause.

FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

The three important concepts are:

• OVER() → Defines the window.

• PARTITION BY → Divides rows into groups.

• ORDER BY → Defines the order inside each group.

🔹 3. ROW_NUMBER()

Assigns a unique sequential number to each row.

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

Result:

Employee | Department | Salary | Row_Num
Mike     | IT         | 90,000 | 1
Lisa     | IT         | 90,000 | 2
John     | IT         | 75,000 | 3
Alice    | HR         | 70,000 | 1
Sarah    | HR         | 60,000 | 2

⚠️ If salaries are tied, ROW_NUMBER() still assigns different numbers.

🔹 4. RANK()

Gives the same rank to tied values.

For: 100, 100, 90

RANK() produces: 1, 1, 3

The next rank is skipped.

RANK() OVER (
    PARTITION BY Department
    ORDER BY Salary DESC
) AS Salary_Rank

🔹 5. DENSE_RANK()

Also gives the same rank to tied values, but doesn't skip the next rank.

For: 100, 100, 90

DENSE_RANK() produces: 1, 1, 2

🧠 Remember the Difference

For values: 100, 100, 90, 80

Function     | Result
ROW_NUMBER() | 1, 2, 3, 4
RANK()       | 1, 1, 3, 4
DENSE_RANK() | 1, 1, 2, 3

This difference is a very common SQL interview topic.

🔹 6. Overall Ranking

Remove PARTITION BY when you want to rank across the entire dataset.

SELECT
    Employee,
    Salary,
    RANK() OVER (
        ORDER BY Salary DESC
    ) AS Overall_Rank
FROM Employees;

🔹 7. Top N Employees Per Department

One of the most useful real-world applications.

WITH Ranked_Employees AS (
    SELECT
        Employee,
        Department,
        Salary,
        ROW_NUMBER() OVER (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS rn
    FROM Employees
)

SELECT
    Employee,
    Department,
    Salary
FROM Ranked_Employees
WHERE rn <= 2;

This finds the top 2 employees in every department.

This pattern is extremely important:

Window Function → CTE/Subquery → Filter

🔹 8. LAG()

LAG() lets you access a value from a previous row.

For monthly sales:

Month | Sales
Jan   | 10,000
Feb   | 12,000
Mar   | 15,000

SELECT
    Sales_Month,
    Sales,
    LAG(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Previous_Month_Sales
FROM Monthly_Sales;