Advanced SQL: Using Window Functions

Hello, SQL enthusiasts!

Welcome back to SheSpeaksSQL! Today, we’re delving into the world of advanced SQL with a focus on window functions. Window functions are powerful tools that allow you to perform calculations across a set of table rows related to the current row, providing advanced analytical capabilities without needing to group your data. Let’s explore window functions with examples and use cases to enhance your SQL skills.

What Are Window Functions?

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row, and rows retain their separate identities.

Key Components of Window Functions

PARTITION BY: Divides the result set into partitions to which the window function is applied.
ORDER BY: Defines the order of rows within each partition.
OVER(): Specifies the window over which the function operates.

Common Window Functions

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
RANK(): Assigns a rank to each row within a partition, with gaps in ranking for ties.
DENSE_RANK(): Similar to RANK(), but without gaps in ranking.
NTILE(n): Divides rows into a specified number of approximately equal groups.
LAG() and LEAD(): Accesses data from a previous or following row in the same result set.
SUM(), AVG(), MIN(), MAX(): Standard aggregate functions that can be used as window functions.

Examples and Use Cases

1. ROW_NUMBER()

Use Case: Assigning unique row numbers to each row in a result set.

Example:

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

This query assigns a unique row number to each employee within their department, ordered by salary in descending order.

2. RANK() and DENSE_RANK()

Use Case: Ranking rows within partitions.

Example:

SELECT 
    EmployeeID, 
    Salary, 
    RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM 
    Employees;

This query ranks employees within each department by their salary. If two employees have the same salary, they will receive the same rank.

3. NTILE()

Use Case: Dividing rows into a specified number of groups.

Example:

SELECT 
    EmployeeID, 
    Salary, 
    NTILE(4) OVER(ORDER BY Salary DESC) AS Quartile
FROM 
    Employees;

This query divides employees into four quartiles based on their salary.

4. LAG() and LEAD()

Use Case: Accessing data from a previous or next row in the result set.

Example:

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

This query shows the previous and next salary for each employee in the result set.

5. SUM(), AVG(), MIN(), MAX()

Use Case: Calculating running totals, averages, and other aggregations.

Example:

SELECT 
    EmployeeID, 
    Salary, 
    SUM(Salary) OVER(PARTITION BY Department ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM 
    Employees;

This query calculates a running total of salaries within each department.

Practice Problems

To help you master window functions, here are a few practice problems:

Rank Employees: Write a query to rank employees within each department by their hire date.
Calculate Running Average: Write a query to calculate the running average of sales amounts for each salesperson.
Identify Top-N Records: Write a query to identify the top 3 highest-paid employees in each department.
Find Sales Trends: Write a query using LAG() to identify the month-over-month sales trends for each product.

I encourage you to share your solutions and experiences in the comments below. How have you used window functions in your projects? What challenges did you face, and how did you overcome them? Let’s learn and grow together!

Happy coding!

Lindsay

SheSpeaksSQL