SQL Window Functions Practical Case Study

Introduction

SQL window functions are a powerful feature in SQL that allow users to perform complex data analysis by enabling the calculation of values across a set of rows, typically ordered by a specified column. Unlike aggregate functions (e.g., SUM, AVG, COUNT) that return a single value per group, window functions can compute values across a partition of the dataset, often with the ability to rank, order, or calculate running totals. This article explores the practical application of window functions through a case study involving employee data, demonstrating how to leverage these functions for real-world scenarios.

Core Concepts

Window functions operate on a set of rows, often referred to as a partition, and are defined using the OVER clause. They can include parameters such as ORDER BY, ROW_NUMBER(), RANK(), DENSE_RANK(), COUNT, SUM, AVG, MIN, MAX, and PERCENT_RANK. These functions are particularly useful for tasks like ranking employees, calculating moving averages, or determining the average salary per department.

Key Components

  1. Partitioning: Rows are divided into partitions based on a specified column (e.g., department).
  2. Ordering: Rows within a partition are sorted using an ORDER BY clause.
  3. Window Function: A function applied to the partition, such as ROW_NUMBER() to assign a unique identifier to each row within the partition.

Differences from Aggregate Functions

While aggregate functions (e.g., SUM, AVG) return a single value per group, window functions can return multiple values per row, enabling row-level calculations. For example, ROW_NUMBER() assigns a unique number to each row within a partition, while RANK() assigns a rank to rows with ties.

Syntax and Examples

To use window functions, the OVER clause is applied to a SELECT statement. The general syntax is:

SELECT column1, column2, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, 
       RANK() OVER (ORDER BY salary DESC) AS rank_num 
FROM employees;

Example 1: Ranking Employees by Salary

This query ranks employees by their salary in descending order.

SELECT 
    employee_id, 
    name, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS salary_rank 
FROM employees;
  • Output:
    employee_id | name     | salary | salary_rank  
----------- |----------|--------|------------  
101         | Alice    | 15000  | 1          
102         | Bob      | 14000  | 2          
103         | Charlie  | 13000  | 3          
104         | David    | 12000  | 4          
105         | Eve      | 11000  | 5
  • Explanation: RANK() assigns the same rank to employees with identical salaries, ensuring ties are grouped.

Example 2: Calculating Running Totals

This query calculates the cumulative sum of salaries as we iterate through the dataset.

SELECT 
    employee_id, 
    name, 
    salary, 
    SUM(salary) OVER (ORDER BY salary DESC) AS running_total 
FROM employees;
  • Output:
    employee_id | name     | salary | running_total  
----------- |----------|--------|--------------  
101         | Alice    | 15000  | 15000        
102         | Bob      | 14000  | 29000        
103         | Charlie  | 13000  | 42000        
104         | David    | 12000  | 54000        
105         | Eve      | 11000  | 65000
  • Explanation: SUM(salary) OVER (ORDER BY salary DESC) computes the cumulative sum of salaries in descending order.

Example 3: Row Numbers

This query assigns a unique row number to each employee within their department.

SELECT 
    employee_id, 
    name, 
    salary, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num 
FROM employees;
  • Output:
    employee_id | name     | salary | row_num  
----------- |----------|--------|--------  
101         | Alice    | 15000  | 1      
102         | Bob      | 14000  | 2      
103         | Charlie  | 13000  | 3      
104         | David    | 12000  | 4      
105         | Eve      | 11000  | 5
  • Explanation: ROW_NUMBER() ensures each employee gets a unique identifier within their department, even if multiple employees have the same salary.

Practical Case Study

Scenario: Employee Data

Consider a table employees with the following columns:

  • employee_id (primary key)
  • name
  • salary
  • department

The goal is to analyze employee salaries across departments, calculate average salaries, and rank employees by salary.

Query 1: Average Salary per Department

This query calculates the average salary for each department.

SELECT 
    department, 
    AVG(salary) OVER (PARTITION BY department) AS avg_salary 
FROM employees;
  • Output:
    department | avg_salary  
---------- |------------  
Sales      | 13500        
R&D        | 12000        
Marketing  | 14000
  • Explanation: AVG(salary) OVER (PARTITION BY department) computes the average salary for each department.

Query 2: Top 10% Salary Rank

This query ranks employees by salary, identifying those in the top 10% (i.e., the top 10% of salaries in their department).

SELECT 
    employee_id, 
    name, 
    salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank 
FROM employees 
ORDER BY department, salary DESC;
  • Output:
    employee_id | name     | salary | rank  
----------- |----------|--------|------  
101         | Alice    | 15000  | 1    
102         | Bob      | 14000  | 2    
103         | Charlie  | 13000  | 3    
104         | David    | 12000  | 4    
105         | Eve      | 11000  | 5
  • Explanation: RANK() assigns ranks to employees, with ties receiving the same rank. The top 10% are identified by their ranks.

Query 3: Row Numbers for Department Analysis

This query assigns a unique row number to each employee within their department, facilitating analysis of salary trends.

SELECT 
    employee_id, 
    name, 
    salary, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num 
FROM employees;
  • Output:
    employee_id | name     | salary | row_num  
----------- |----------|--------|--------  
101         | Alice    | 15000  | 1      
102         | Bob      | 14000  | 2      
103         | Charlie  | 13000  | 3      
104         | David    | 12000  | 4      
105         | Eve      | 11000  | 5
  • Explanation: ROW_NUMBER() ensures each employee is uniquely identified within their department, even if multiple employees have the same salary.

Conclusion

Window functions in SQL provide a flexible and powerful way to perform complex data analysis by enabling calculations across partitions of data. Through practical examples, this case study demonstrates how to use functions like RANK(), ROW_NUMBER(), and AVG() to rank employees, calculate running totals, and analyze salary trends. By leveraging these functions, users can gain deeper insights into their data, such as identifying top performers, calculating averages, and ranking employees within departments. Understanding and applying window functions is essential for effective data manipulation and analysis in SQL.