Write a SQL query to find all numbers that appear at least three times

Question

I'm practicing SQL language and got a question like:

Write a SQL query to find all numbers that appear at least three times consecutively.

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

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

I got a solution online and test it. But I really do not understand it. The big picture of the solution is clear. sq table counts the occurrences. But I did not understand the part of computing the sq. I've done a lot of research on the MYSQL. @counter := IF(@prev = Num, @counter + 1, 1) means if prev = Num, making counter = counter + 1, otherwise counter = 1. (SELECT @counter:=1, @prev:=NULL) vars means create a table vars which includes two columns counter and pre.

Can anyone help me explain the logic of the sq part? Or is there any tutorial for this kind of expressions in the SELECT? I'm totally new to SQL and I know this question may be pretty simple. Thanks for your help!

SELECT  DISTINCT(Num) AS ConsecutiveNums
FROM (
    SELECT
    Num,
    @counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
    @prev := Num
    FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars
) sq
WHERE how_many_cnt_in_a_row >= 3

Answer 1

Another approach

WITH x AS (
    SELECT a.id, a.num
    FROM Logs AS a
    INNER JOIN Logs AS b ON
        a.num = b.num AND 
        b.id - a.id = 1
)
SELECT DISTINCT m.num AS ConsecutiveNums
FROM (
    SELECT x.num
    FROM x
    INNER JOIN x AS y ON
        x.num = y.num AND
        y.id - x.id = 1
) AS m;

Answer 2

SELECT DISTINCT Num
FROM (
  SELECT Num,
         LAG(Num) OVER (ORDER BY Id) AS prev_num,
         LEAD(Num) OVER (ORDER BY Id) AS next_num
  FROM your_table
) AS subquery
WHERE Num = prev_num AND Num = next_num;

Answer 3

Another solution to check if next ids have the same value as current id

SELECT DISTINCT Num as ConsecutiveNums
FROM Logs
WHERE (Id + 1, Num) IN (SELECT * FROM Logs) AND (Id + 2, Num) IN (SELECT * FROM Logs)

Answer 4

select distinct num from ( select num,
                            lag(num,1) over(order by ID) lag1,
                            lead(num,1) over(order by ID) lead1
                            from Table
                            ) as a
    where num=lag1 and num=lead1
    and lag1=lead1

Answer 5

SELECT 
    distinct A.num as ConsecutiveNums 
FROM
    LOGS A
INNER JOIN Logs B
    on A.id=b.id+1
INNER join Logs C
    on b.id=c.id+1
WHERE a.num=b.num AND a.num=c.num
;

Answer 6

Here is another version of the answer...

select distinct num 
  from ( 
          select num
                ,lag(num,1) over() as lag1
                ,lag(num,2) over() as lag2
             from
                 logs
       ) as a
   where  num = lag1 = lag2
     and  lag1 = lag2

Answer 7

Try the following query:

select ConsecutiveNums from(    
        select 
        case 
        when lag(Num) over (order by Id) = Num and Num=lead(Num) over (order by 
        Id)  then Num 
        end as ConsecutiveNums
        from Logs
        )  
where ConsecutiveNums is not null

Answer 8

Let's go through each record and see how this query works. It's well written.

SELECT...FROM

What does SELECT...FROM Logs y, (...) vars mean?

If you had a table like this: create table test(field1 int) that contains 3 rows like so:

field1
-------
1
2
3

Doing select * from test, (select @counter:=1, @prev:=NULL) vars will result in

field1  @counter:=1  @prev=NULL
------- ------------ -----------
1       1            NULL
2       1            NULL
3       1            NULL

@counter and @prev are session variables. They are initialized to 1 and NULL respectively. All rows are combined with these variables to give you what you see above.

Row by row analysis of the subquery

Focus on just this subquery.

SELECT
Num,
@counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
@prev := Num
FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars

The query selects the first row of ID=1, Num=1, and chooses Num as it's first column.

For the 2nd column, it does some math. It checks if @prev = Num. Well, @prev is NULL because that's how it was initialized. So, @prev = Num results in false. IF is generally written as IF(condition, what-to-do-if-condition-is-true, what-to-do-if-condition-is-false).

IF(@prev = Num, @counter + 1, 1)
   -----------  ------------  --
   condition    do this       do this if condition
                if true       is false

Since @prev is NULL and not equal to Num, 1 is returned.

For the 3rd column, the query just resets @prev to Num. That's really all. Now let's see how SELECT goes line by line and does its magic.

Num  @prev was  @counter was  @counter calculation      @prev reset to Num
---  ---------  ------------  -----------------------   ------------------
1    NULL       1             is @prev = 1? No. So 1      1
1    1          1             is @prev = 1? Yes! So 2     1
1    1          2             is @prev = 1? Yes! So 3     1
2    1          3             is @prev = 2? No. So 1      2
1    2          1             is @prev = 1? No. So 1      1
2    1          1             is @prev = 2? No. So 1      2
2    2          1             is @prev = 2? Yes! So 2     2

The 2nd and 3rd column above are for understanding purposes only.

Now that the subquery has done its job, SELECT DISTINCT... comes and asks: From the result above, give me only the row that has @counter of 3 or higher. The result is going to be

Num   @counter  @prev
----  --------  -----
1     3         1

If your dataset had five 1s one after the other, 3rd, 4th and 5th 1 will be retrieved. Therefore, DISTINCT(Num) is used to select only a single 1. It's just smart thinking. It may be possible to change the WHERE clause to read WHERE ... = 3 insted of >= 3.

Hope this makes sense.

Answer 9

First, the line below is simply initializing variables @counter and @prev. For more information about it check User Defined Variables.

(SELECT @counter:=1, @prev:=NULL)

So, sq is not an actual table, but it works as an alias so you can reference these in-memory variables.@counter variable counts how many numbers are in consecutive order, when the previous number @prev is different from the actual one Num, @counter is reset to 1 and the counting process starts again.

In order to be clearer, here is what would be the values for sq:

+-----+-----------------------+ | Num | how_many_cnt_in_a_row | +-----+-----------------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 1 | 1 | | 2 | 1 | | 2 | 2 | +-----+-----+