Introduction

In this post, we explore the concept of gaps in sequential data and how Window Functions can be leveraged to identify and analyze them effectively.

Refresher on Window Functions

Window Functions were introduced in ANSI SQL:2003. These functions allow computations across a set of table rows that are related to the current row using the OVER clause.

A common use case for window functions is analytical queries, particularly in identifying islands and gaps in datasets:

• Islands: Groups of continuous data sequences.
• Gaps: Missing sequences between data points.

Problem Scenario

Consider a scenario where a patient enrolled in a health program is required to check in daily. Our objective is to identify the gaps in their consecutive visits.

Dataset

Sample Data

Creating the Dataset in SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

DROP TABLE IF EXISTS #table2;

CREATE TABLE #table2(visit_date DATE);

INSERT INTO #table2 (visit_date)
VALUES
    ('2025-02-02'),
    ('2025-02-03'),
    ('2025-02-11'),
    ('2025-02-12'),
    ('2025-02-13'),
    ('2025-02-27'),
    ('2025-04-03'),
    ('2025-04-04'),
    ('2025-04-05'),
    ('2025-05-12');

Expected Output

After analyzing gaps, we should obtain the following missing date ranges:

Algorithmic Approach

To identify gaps, we calculate the difference in days between each visit. If the gap is greater than one day, it indicates a missing period.

Step 1: Compare Each Visit to the Next

Step 2: Compute Days Between Visits

Any difference greater than 1 day represents a gap.

SQL Implementation

We use the LEAD Window Function to fetch the next visit date for comparison.

1
2
3

SELECT visit_date AS startDate,
       LEAD(visit_date) OVER (ORDER BY visit_date) AS endDate
FROM #table2;

Output

Final Query

The final query identifies gaps by filtering where the difference is greater than 1 day:

1
2
3
4
5
6
7
8
9

WITH CTE_LEAD AS (
    SELECT visit_date AS startDate,
           LEAD(visit_date) OVER (ORDER BY visit_date) AS endDate
    FROM #table2
)
SELECT DATEADD(DAY, 1, startDate) AS startDate,
       DATEADD(DAY, -1, endDate) AS endDate
FROM CTE_LEAD
WHERE DATEDIFF(DAY, startDate, endDate) > 1;

Explanation

• The CTE_LEAD common table expression fetches the next visit date.
• The outer query filters out rows where the gap is more than one day.
• The DATEADD() function adjusts the start and end dates to reflect the missing range.

Final Output

Conclusion

This post demonstrated how Window Functions help identify gaps in sequential data efficiently, making them a powerful tool for analytical SQL queries.

References

• https://data-mozart.com/island-adventures-with-t-sql-window-functions/
• https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql