Write T-SQL query to find all numbers that appear at least three times consecutively

user_13976369 0
Viewed 429

This is the question from Leetcode. I've done it with MySQL, but I don't really understand how to convert it into T-sql query.

For example, given the Logs table, 1 is the only number that appears consecutively for at least three times.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

My logic with MySQL is like this:

SELECT DISTINCT Num AS ConsecutiveNums
  FROM(
 SELECT Num, @times := IF(@prev = Num, @times + 1, 1) AS Consecutivetimes, @prev := Num
   FROM Logs, (SELECT @times := 1, @prev := -1) AS init) AS p
 WHERE Consecutivetimes >= 3

But how should the assignment @times := IF(@prev = Num, @times + 1, 1) AS Consecutivetimes, @prev := Num be written with T-SQL?

I've tried to use SELECT to assign value.

DECLARE @times INT, @prev INT;
SET @times = 1;
SET @prev = -1;
SELECT DISTINCT Num AS ConsecutiveNums
FROM(
    SELECT Num,
    (SELECT CASE WHEN @prev = Num
          THEN
            BEGIN
                @times = @times + 1 
                @prev = Num
            END
          ELSE
            BEGIN
                @times = 1
                @prev = Num
            END 
          END) AS Consecutivetimes
    FROM Logs) t
WHERE Consecutivetimes >= 3;
tsql sql-server sql
1 Answers

I would use lag():

select distinct num
from (select l.*,
             lag(num) over (order by id) as prev_num,
             lag(num, 2) over (order by id) as prev2_num
      from logs l
     ) n
where prev_num = num and prev2_num = 2;
user_11440350
Related