SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial

Introduction

This tutorial focuses on SQL Window Functions, also known as Analytic Functions, which are used to perform calculations across a set of table rows that are related to the current row. Understanding these functions is essential for analyzing data within SQL databases. In this guide, we will cover the syntax and usage of key window functions including RANK, DENSE RANK, ROW_NUMBER, LEAD, and LAG, along with the OVER clause and its components.

Step 1: Understanding Aggregate Functions

• Definition: Aggregate functions perform a calculation on a set of values and return a single value.
• Common Aggregate Functions:
  • MIN: Returns the smallest value.
  • MAX: Returns the largest value.
  • SUM: Returns the total sum.
  • COUNT: Returns the count of rows.
  • AVG: Returns the average value.

Step 2: Writing SQL Queries with Window Functions

• Basic Syntax:

  SELECT column1, 
         window_function(column2) OVER (
             PARTITION BY column3 
             ORDER BY column4
         ) AS alias_name
  FROM table_name;
  

• Components:
  • 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.

Step 3: Using ROW_NUMBER() Window Function

• Purpose: Assigns a unique sequential integer to rows within a partition of a result set.
• Example:

  SELECT column1, 
         ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) AS row_num
  FROM table_name;
  

Step 4: Using RANK() Window Function

• Purpose: Assigns a rank to each row within a partition, with gaps in the ranking for ties.
• Example:

  SELECT column1, 
         RANK() OVER (PARTITION BY column2 ORDER BY column3) AS rank_num
  FROM table_name;
  

Step 5: Using DENSE_RANK() Window Function

• Purpose: Similar to RANK, but without gaps in the ranking for ties.
• Example:

  SELECT column1, 
         DENSE_RANK() OVER (PARTITION BY column2 ORDER BY column3) AS dense_rank_num
  FROM table_name;
  

Step 6: Comparing RANK, DENSE_RANK, and ROW_NUMBER

• Differences:
  • ROW_NUMBER: Unique sequence number for each row.
  • RANK: Gaps in ranking for duplicate values.
  • DENSE_RANK: No gaps in ranking for duplicate values.

Step 7: Using LEAD() and LAG() Window Functions

• LEAD(): Accesses data from the next row in the result set.
• LAG(): Accesses data from the previous row in the result set.
• Example:

  SELECT column1, 
         LAG(column2, 1) OVER (ORDER BY column3) AS previous_value,
         LEAD(column2, 1) OVER (ORDER BY column3) AS next_value
  FROM table_name;
  

Conclusion

In this tutorial, we explored SQL Window Functions and their applications. We covered how to use the ROW_NUMBER, RANK, DENSE_RANK, LEAD, and LAG functions, alongside the OVER clause for creating partitions and ordering data. Understanding these functions enhances your ability to analyze data effectively within SQL databases. For further practice, consider downloading the SQL scripts provided in the video and experimenting with various queries.