Introduction

Window Functions were introduced in ANSI SQL:2003, making them a relatively new feature. They allow functions to be applied to a set of rows using the OVER clause.

One of the most common use cases for window functions is analytical queries, specifically identifying islands and gaps in a dataset.

• Islands are groups of continuous data sequences.
• Gaps are missing sequences between data points.

Dataset

Here’s the initial dataset:

Creating the Dataset

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DROP TABLE IF EXISTS table1;

CREATE TEMPORARY TABLE table1(
  col1 INT NOT NULL GENERATED BY DEFAULT AS IDENTITY
);

INSERT INTO table1(col1)
VALUES (2), (3), (11), (12), (13), (27), (33), (34), (35), (42);

SELECT col1
FROM table1
ORDER BY col1;

Expected Output

After identifying islands in the dataset, we should get the following result:

Algorithmic Approach

To solve this, we need to group numbers that belong to the same continuous sequence (island).

Each group (grp) represents an island, where numbers belong to a continuous sequence. For example, group D represents the sequence 33, 34, 35.

SQL Implementation

We can use window functions to achieve this:

1
2
3
4

SELECT 
  col1,
  col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
FROM table1;

Output

Explanation

• By subtracting ROW_NUMBER() OVER(ORDER BY col1) from col1, we create a unique identifier (grp) for each island.
• Numbers in the same sequence get the same grp value.

Final Query

Now, we just need to group by the grp column to get the start and end of each island:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

WITH grp AS (
  SELECT 
    col1,
    col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
  FROM table1
)
SELECT 
  MIN(col1) AS start_range, 
  MAX(col1) AS end_range
FROM grp
GROUP BY grp
ORDER BY start_range;

Final Output

Conclusion

By leveraging window functions, we efficiently identify islands in a dataset using row numbering and grouping techniques. This method can be extended to various real-world use cases, such as:

• Tracking continuous patient visits in healthcare analytics.
• Identifying consecutive login days in user behavior analysis.
• Detecting gaps in financial transactions for fraud detection.

Credits

Pictures

1. Easter island

References

1. SQL Example were taken from “T-SQL Window Functions, Second Edition For data analysis and beyond” by Itzik Ben-Gan