medium Repeated Values in Sequence

A value sits in the middle of a run of three-or-more identical values if and only if its immediate predecessor and immediate successor (by id order) both equal it — so LAG/LEAD let one pass over the table test that local three-window per row, and any qualifying run leaves at least one such interior row behind.

The mechanism, stated precisely

The task is “values appearing at least 3 times consecutively.” The clever reframing: don't try to measure run length. Instead notice a structural invariant about runs.

Invariant: a maximal run of length L (all the same value, contiguous by id) contains an interior element — one whose left neighbor and right neighbor are both inside the same run — exactly when L ≥ 3. The first and last members of a run are boundary elements; everything strictly between them is interior. A run of length 3 has exactly one interior element; a run of length 4 has two; length L has L−2.

So the per-row test value = prev_value AND value = next_value fires precisely on interior rows. Collapsing those with DISTINCT gives the set of values that have at least one run of length ≥ 3. That is the answer — no streak counting, no self-join, no recursion.

WITH Consecutive AS (
    SELECT value,
           LAG(value)  OVER (ORDER BY id) AS prev_value,
           LEAD(value) OVER (ORDER BY id) AS next_value
    FROM Sequence
)
SELECT DISTINCT value AS ConsecutiveNumbers
FROM Consecutive
WHERE value = prev_value
  AND value = next_value;

LAG(value) defaults to offset 1, so LAG(value) and LAG(value, 1) are identical; the explicit 1 is optional. The ORDER BY id inside the window is load-bearing — it defines what “previous” and “next” mean. Without it the neighbor relationship is undefined and results are nondeterministic.

Worked trace on the example

Input Sequence: ids 1–7 with values 100,100,100,200,100,200,200. The CTE computes prev_value and next_value per row by sliding the window in id order. Endpoints get NULL (no neighbor), which is the desired behavior — NULL never equals anything, so an endpoint can never be flagged as interior.

Exactly one row survives the filter: id 2, value 100 — the single interior element of the only run of length ≥ 3. DISTINCT is a no-op here (one row), but it is what keeps a longer run from emitting the value multiple times. Final output: a one-row table with 100.

Why this is the “gaps and islands” family

This problem is the simplest instance of a classic pattern called gaps and islands: a sequence partitions into islands (maximal runs of the same value) separated by gaps (value changes). Most questions in this family ask about island length — longest streak, runs of at least k, start/end of each run.

The general, reusable technique is the difference-of-row-numbers trick: number all rows, number rows within each value group, and subtract — the difference is constant within one island, so you can GROUP BY (value, diff) and use COUNT(*) to get exact run lengths.

-- General: exact run length, works for ANY threshold k
WITH islands AS (
    SELECT value,
           ROW_NUMBER() OVER (ORDER BY id)
         - ROW_NUMBER() OVER (PARTITION BY value ORDER BY id) AS grp
    FROM Sequence
)
SELECT DISTINCT value AS ConsecutiveNumbers
FROM islands
GROUP BY value, grp
HAVING COUNT(*) >= 3;

The LAG/LEAD solution is the special-case shortcut that works only because the threshold is exactly 3 and 3 = a window of width 3 you can peek at directly. For “at least 5 consecutive” the peek trick needs LAG(value,1),LAG(value,2),… or the general island grouping. Recognizing which one a question wants is the actual skill.

Pitfalls

• Omitting ORDER BY id in the window. Without it, “previous” and “next” are undefined; the planner may use insertion or page order and silently give wrong, non-reproducible results. The ordering is the definition of consecutiveness, not decoration.
• Assuming id is gapless/contiguous. The neighbor by ORDER BY id is the next row in sort order, not id+1. Rows 10,11,40 are still “consecutive” for this query. If the problem means “consecutive timestamps with no gap,” you must add a gap check — LAG alone won't catch it.
• Dropping DISTINCT. A run of length 4 has two interior rows, length 5 has three; each emits the value once. Without DISTINCT the same value repeats in the output. It looks redundant on the 3-row example and bites on real data.
• Forgetting NULL semantics at the edges. This one is a happy accident: 100 = NULL is UNKNOWN, treated as false, so endpoints can't be interior. But the same NULL logic burns you if value itself can be NULL — three consecutive NULLs would not be flagged, because NULL = NULL is UNKNOWN. Use IS NOT DISTINCT FROM (Postgres) or null-safe equality if NULL values must count.
• Naive self-join instead. A common first attempt joins the table to itself on id = id+1 and id = id+2. It breaks the moment ids have gaps (the id+1 row may not exist) and is O(n) joins vs one O(n log n) window pass. The window approach is both correct under gaps and faster.

Takeaways

• Don't count run length when you only need a threshold — reframe “≥ 3 consecutive” as “has an interior element,” which a fixed LAG+LEAD window detects in one pass.
• The invariant (run of length L has L−2 interiors) is why this is correct and why DISTINCT is mandatory: long runs surface multiple interior rows for the same value.
• ORDER BY inside the window defines consecutiveness; neighbors are by sort position, not by id arithmetic — mind gaps and NULLs.
• For any other threshold k, reach for the gaps-and-islands ROW_NUMBER() difference trick; the peek-3 query is just its degenerate special case.

Re-authored and deepened for this guide. The interior-element invariant, the gaps-and-islands framing, and the difference-of-row-numbers generalization draw on Itzik Ben-Gan's treatment of gaps and islands in Microsoft SQL Server T-SQL Querying (Microsoft Press) and the SQL window-function chapter of Use The Index, Luke! (Markus Winand). Window-function NULL and ordering semantics per the PostgreSQL 16 documentation (§ 3.5, 4.2.8) and the SQL:2003 standard. Problem framing follows the LeetCode “consecutive numbers” family. Previous version was correct but omitted the invariant, the run-of-4 duplication note, and the gaps-and-islands generalization.