🔍 SQL Pattern Sleuth: Detecting Consecutive Repetition in Logs

🧩 Problem Overview

In relational datasets, patterns of repetition often signal behavioral trends, anomalies, or classification cues. This challenge from LeetCode, 180. Consecutive Numbers, asks us to identify values that appear at least three times in a row within a log table. It’s a classic use case for window functions—specifically LEAD()—to detect sequential repetition without relying on self-joins.

Schema:

sql

Table: Logs  
+-------------+---------+  
| Column Name | Type    |  
+-------------+---------+  
| id          | int     |  
| num         | varchar |  
+-------------+---------+

The id column is auto-incremented and serves as the primary key. Your task is to return all distinct numbers that appear three times consecutively, regardless of their position in the table.

🧠 SQL Solution Using LEAD()

To detect three consecutive repetitions, we use the LEAD() window function to peek ahead at the next two rows for each entry. This avoids messy self-joins and keeps the logic clean and scalable.

sql

WITH data AS (
  SELECT num, 
         LEAD(num, 1) OVER (ORDER BY id) AS a,
         LEAD(num, 2) OVER (ORDER BY id) AS b
  FROM logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM data
WHERE num = a AND a = b;

🧩 Why It Works

• LEAD(num, 1) and LEAD(num, 2) allow us to compare the current row with the next two rows in sequence.

• If all three values match (num = a AND a = b), we’ve found a streak of three.

• DISTINCT ensures we return each qualifying number only once.

This approach is not only elegant but also highly reusable in annotation pipelines, log analysis, and behavioral pattern detection.

📊 Real-World Applications

• Detecting repeated user actions in clickstream logs

• Identifying sensor anomalies in IoT datasets

• Flagging suspicious activity in audit trails

• Building annotation logic for temporal consistency

✨ Advance SQL Insight

This module is a perfect fit for your reusable SQL toolkit. It demonstrates:

• Mastery of window functions

• Logic-driven filtering

• Clean, dashboard-ready output

Pair it with your streak detection logic or classification modules to build scalable, intelligent workflows.