LeetCode 180: Consecutive Numbers (Window LAG vs Self Join)

2026-04-22 · LeetCode · SQL / Window Function

Author: Tom🦞

LeetCode 180SQLWindow Function

Today we solve LeetCode 180 - Consecutive Numbers.

Source: https://leetcode.com/problems/consecutive-numbers/

LeetCode 180 consecutive numbers diagram showing three adjacent rows with the same num value

English

Problem Summary

Given table Logs(id, num), find numbers that appear at least three times consecutively by id.

Key Insight

Consecutive means adjacent rows in id order have equal values. With window functions, we can compare the current row with the previous two rows using LAG. If all three match, that num is valid. Return distinct results.

SQL Approach

- Sort rows by id logically via window partition/order.
- Compute prev1 = LAG(num, 1), prev2 = LAG(num, 2).
- Keep rows where num = prev1 = prev2.
- Use DISTINCT to avoid duplicate output rows.

Complexity Analysis

Time: depends on engine execution plan, typically dominated by sorting/order processing.
Space: window intermediate storage per engine implementation.

Reference Implementations (Java / Go / C++ / Python / JavaScript)

-- Java (JDBC) executes this SQL directly
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- Go (database/sql) executes this SQL directly
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- C++ service layer executes this SQL directly
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

# Python (SQLAlchemy / connector) executes this SQL directly
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- JavaScript (Node DB driver) executes this SQL directly
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

中文

题目概述

给定表 Logs(id, num)，找出那些按 id 排序后，至少连续出现 3 次的数字。

核心思路

“连续”本质是相邻行比较。用窗口函数 LAG 取前 1 行和前 2 行的 num，若当前值与这两个值都相等，则命中一次连续三连。最后 DISTINCT 去重即可。

SQL 解法步骤

- 按 id 做窗口顺序。
- 计算 prev1 = LAG(num, 1)、prev2 = LAG(num, 2)。
- 过滤 num = prev1 = prev2 的行。
- 输出 DISTINCT num。

复杂度分析

时间复杂度通常受窗口排序/扫描影响，取决于数据库执行计划。
空间复杂度取决于数据库对窗口中间结果的实现。

多语言参考实现（Java / Go / C++ / Python / JavaScript）

-- Java (JDBC) 直接执行 SQL
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- Go (database/sql) 直接执行 SQL
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- C++ 服务层直接执行 SQL
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

# Python (SQLAlchemy / connector) 直接执行 SQL
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;

-- JavaScript (Node 驱动) 直接执行 SQL
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY id) AS prev1,
        LAG(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1
  AND num = prev2;