LeetCode 180 Consecutive Numbers works with a small Logs table that records an Id and a Num value on every row. The goal is to return the numbers that appear on at least three rows in a row when you sort by Id, so a value that shows up at positions 5, 6, and 7 in that order qualifies, while scattered repeats with gaps do not. The full statement on LeetCode spells out the table definition and examples, but the heart of the question is detecting three row streaks for the same Num and reporting which values achieve that streak.
Thinking about this problem in SQL Server means treating Logs as an ordered sequence instead of an unordered bag of rows and then asking how to compare each row with its neighbors. One line of thought is to write joins that match a row with the next one and the next one after that by Id, while a second line of thought relies on T-SQL window functions such as ROW_NUMBER or LAG to tag or inspect streaks in a single pass. In both cases the mental model stays the same, sort by Id, pick out runs where Num repeats without gaps, and then keep the values whose runs reach a length of at least three, all while keeping an eye on indexes and how the engine walks that ordered data.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column starting from 1.
Find all numbers that appear at least three times consecutively.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.
Solution 1: Self Join on Three Adjacent Ids
This method treats Logs as ordered by Id and lines up three neighboring rows that share the same Num. It follows the problem statement very closely and keeps the SQL in a form that feels familiar to anyone who has worked with joins.
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2
ON l2.Id = l1.Id + 1
AND l2.Num = l1.Num
JOIN Logs l3
ON l3.Id = l2.Id + 1
AND l3.Num = l2.Num;
Let’s break that query down:
SELECT DISTINCT
l1.Num AS ConsecutiveNums
This part asks SQL Server to return one column named ConsecutiveNums, which comes from l1.Num. The DISTINCT keyword removes duplicate rows in the final result. That detail matters when a number appears in a run longer than three rows, because several overlapping triples can satisfy the join conditions. Without DISTINCT the same Num would show up several times in the output, but LeetCode expects each qualifying number to appear once.
FROM Logs l1
This line picks Logs as the base table and gives it the alias l1. Every row in Logs becomes a candidate starting point for a three row streak. Column l1.Id acts as the starting position in that streak, and l1.Num is the number that needs to repeat on the next two rows.
JOIN Logs l2
ON l2.Id = l1.Id + 1
AND l2.Num = l1.Num
Here the query brings in a second copy of Logs with alias l2. Condition l2.Id = l1.Id + 1 links a starting row to the row whose Id is exactly one greater. On LeetCode, id is an autoincrement primary key, so the next row in Id order is typically Id + 1. In a general table where Id values can have gaps, Id + 1 means “next integer” rather than “next row”, so this join style depends on that no gap assumption. Condition l2.Num = l1.Num keeps only those pairs where the number is the same on both rows. After this join, any surviving pair represents at least a two row streak for that Num, with consecutive Id values.
JOIN Logs l3
ON l3.Id = l2.Id + 1
AND l3.Num = l2.Num
This third copy of Logs, aliased as l3, extends the candidate streak to a third row. Condition l3.Id = l2.Id + 1 moves one more step forward in Id order. Condition l3.Num = l2.Num insists that the number still has not changed. For any triple of rows that satisfies both joins, the table holds three entries with ids x, x + 1, and x + 2 and all three rows carry the same Num. That combination matches the requirement of three consecutive rows with the same number.
The final result comes from those joined triples. The query projects l1.Num under the name ConsecutiveNums, and DISTINCT removes repeats so that each number that appears in at least one three row streak shows up once in the output.
On SQL Server this self join usually runs as nested loops driven by the primary key on Id, with Logs read as l1 and two B tree seeks per row to find l2 at Id + 1 and l3 at Id + 2, which keeps the core join work around O(R log R) time for R rows, then DISTINCT adds either a hash aggregate with about O(R) expected time or a sort with about O(R log R), and memory stays small for the join probes themselves while the aggregate phase needs roughly O(D) space for D distinct Num values, up to O(R) in the extreme case. When the optimizer cannot rely on the Id index it tends to pick plans built around scanning and sorting or hashing instead of repeated random probes, which keeps the cost around O(R log R) rather than drifting toward quadratic behavior. For an interview this answer works well because it uses basic JOIN syntax that most SQL Server codebases already use heavily, it shows that you read the schema and let the Id primary key act as the ordering signal, and it gives you a chance to talk through how nested loops joins and DISTINCT interact with indexes on a table like Logs.
Solution 2: Window Function with LAG Based Comparison
SQL Server window functions let the query look backward from each row without extra joins. This method uses LAG to peek at the previous two Num values in Id order and keeps only cases where all three match.
SELECT DISTINCT
Num AS ConsecutiveNums
FROM (
SELECT
Id,
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;
Now let’s break this query down:
SELECT DISTINCT
Num AS ConsecutiveNums
This projection asks for one output column named ConsecutiveNums, which comes straight from Num. The DISTINCT keyword removes repeated rows in the final result. When a value forms several qualifying streaks in different parts of the table, the filter keeps that value once, which matches the LeetCode requirement that each number appear a single time if it satisfies the condition.
FROM (
SELECT
Id,
Num,
LAG(Num, 1) OVER (ORDER BY Id) AS prev1,
LAG(Num, 2) OVER (ORDER BY Id) AS prev2
FROM Logs
) t
The FROM clause takes its data from a derived table t. That inner query reads Logs, computes two extra columns with LAG, and passes the enriched rows to the outer query. Every row now carries its own Num plus the Num values from the previous one and previous two rows in Id order.
SELECT
Id,
Num,
LAG(Num, 1) OVER (ORDER BY Id) AS prev1,
LAG(Num, 2) OVER (ORDER BY Id) AS prev2
Inside t, this SELECT list builds four columns. Id and Num come directly from Logs.
LAG(Num, 1) OVER (ORDER BY Id) looks at the row that appears just before the current row when records are sorted by Id and returns that row’s Num. The result is labeled prev1. On the very first row in Id order there is no earlier row, so prev1 is NULL there.
LAG(Num, 2) OVER (ORDER BY Id) moves two steps back in the same Id ordering and returns that row’s Num as prev2. For the first and second rows in Id order there are not enough earlier rows, so prev2 is NULL in those positions.
For a streak where Num = 7 on three consecutive ids, the third row in that streak ends up with Num = 7, prev1 = 7, and prev2 = 7, while earlier rows in the table have at least one NULL or a different value in those lag columns.
FROM Logs
The inner query pulls its input rows from Logs. Every log entry participates in the window calculation and receives prev1 and prev2 based on neighbors in Id order, so each row in t holds the current number and the two numbers directly before it in that ordered sequence.
) t
WHERE Num = prev1
AND Num = prev2;
After the inner query finishes, the outer query applies this WHERE filter. Condition Num = prev1 requires the current row to match the immediately previous row in Id order. Condition Num = prev2 requires the current row to match the row two steps back. When both comparisons pass, the current row and the two rows just before it all share the same Num and sit next to one another when sorted by Id. The filter drops any row that does not end such a three row streak.
For the rows that survive, the outer SELECT DISTINCT Num AS ConsecutiveNums returns the repeated value and DISTINCT collapses duplicates. Longer streaks, such as four or five consecutive rows with the same Num, can produce several rows that pass the WHERE predicate, but they all carry the same Num, and DISTINCT compresses that down to one result row per qualifying number.
With the window function version SQL Server can read Logs in Id order, run a sequence project style operator that holds a tiny rolling buffer of the previous two Num values for the LAG calls, filter rows where Num, prev1, and prev2 all match, then apply DISTINCT, so the window part itself stays at about O(R) time over R rows with O(1) extra space and the heavy work comes from either a hash aggregate around O(R) expected time or a sort around O(R log R) with memory in the O(D) to O(R) range depending on how many distinct numbers pass the filter. If the engine cannot read rows in Id order from an index it adds a sort first at about O(R log R) time and O(R) space, then the LAG scan remains linear. Compared with the self join version this query trades two index seeks per input row for a single ordered pass that keeps comparisons inside the scan and tends to scale better as Logs grows, and in an interview it usually lands as the more advanced follow up answer because it shows you know window functions, can read execution plans that contain LAG and sequence projects, and can talk about how ordered scans and aggregates behave on a table like Logs.
