find all numbers that appear at least three times consecutively This is a question from leetcode online judge

I tried this 

SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) &gt;1


but got runtime error!!

Anyhelp would be appreciated
First of all, your runtime error is based on COUNT (Num). It should be COUNT(Num)

SAMPLE DATA

mysql&gt; DROP DATABASE IF EXISTS guru;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; CREATE DATABASE guru;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE guru
Database changed
mysql&gt; CREATE TABLE logs
    -&gt; (id int not null auto_increment,
    -&gt; num int not null,
    -&gt; primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql&gt; INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)


YOUR QUERY EXECUTED

mysql&gt; SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) &gt; 1;
+-----+
| Num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql&gt;


LOOK AT THE QUESTION AGAIN

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


From the output in your question, 2 does not occur three times consecutively.

I have an iterative solution using user-defined variables

MY PROPOSED QUERY

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT DISTINCT num FROM
(
    SELECT grp,num,MAX(samecount) same FROM
    (
        SELECT
            (@group := @group + IF(@curnum=num,0,1)) grp,
            (@same  := IF(@curnum=num,@same+1,1)) samecount,
            (@curnum:=num),num
        FROM logs ORDER BY id
    ) AA GROUP BY grp,num HAVING same &gt;= 3
) A;


Looks messy, right ? Here is is executed

MY PROPOSED QUERY EXECUTED

mysql&gt; SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT DISTINCT num FROM
    -&gt; (
    -&gt;     SELECT grp,num,MAX(samecount) same FROM
    -&gt;     (
    -&gt;         SELECT
    -&gt;             (@group := @group + IF(@curnum=num,0,1)) grp,
    -&gt;             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    -&gt;             (@curnum:=num),num
    -&gt;         FROM logs ORDER BY id
    -&gt;     ) AA GROUP BY grp,num HAVING same &gt;= 3
    -&gt; ) A;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

mysql&gt;


WHY IT WORKS

Let's strip all the aggregation and subquery stuff and start with the innermost query

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT
    (@group := @group + IF(@curnum=num,0,1)) grp,
    (@same  := IF(@curnum=num,@same+1,1)) samecount,
    (@curnum:=num),num
FROM logs ORDER BY id;


When you run this, here is the output

mysql&gt; SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT
    -&gt;     (@group := @group + IF(@curnum=num,0,1)) grp,
    -&gt;     (@same  := IF(@curnum=num,@same+1,1)) samecount,
    -&gt;     (@curnum:=num),num
    -&gt; FROM logs ORDER BY id
    -&gt; ;
+------+-----------+----------------+-----+
| grp  | samecount | (@curnum:=num) | num |
+------+-----------+----------------+-----+
|    1 |         1 |              1 |   1 |
|    1 |         2 |              1 |   1 |
|    1 |         3 |              1 |   1 |
|    2 |         1 |              2 |   2 |
|    3 |         1 |              1 |   1 |
|    4 |         1 |              2 |   2 |
|    4 |         2 |              2 |   2 |
+------+-----------+----------------+-----+
7 rows in set (0.00 sec)

mysql&gt;


Note the column samecount. When samecount reaches 3 or more, the num column has the value that has been repeated 3 or more times.

Adding the aggregation layer, you get this:

mysql&gt; SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT grp,num,MAX(samecount) same FROM
    -&gt; (
    -&gt;     SELECT
    -&gt;         (@group := @group + IF(@curnum=num,0,1)) grp,
    -&gt;         (@same  := IF(@curnum=num,@same+1,1)) samecount,
    -&gt;         (@curnum:=num),num
    -&gt;     FROM logs ORDER BY id
    -&gt; ) AA GROUP BY grp,num HAVING same &gt;= 3;
+------+-----+------+
| grp  | num | same |
+------+-----+------+
|    1 |   1 |    3 |
+------+-----+------+
1 row in set (0.00 sec)

mysql&gt;


After the aggregation, it is possible that a number can be repeated multiple times later on.

Adding the DISTINCT layer on just NUM, you get the end result I said before.

QUERY AGAINST MORE DATA

Let's add more data

INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
INSERT INTO logs (num) VALUES (7),(7),(7),(8),(8),(9),(5);
INSERT INTO logs (num) VALUES (5),(6),(11),(11),(11),(2),(2);
INSERT INTO logs (num) VALUES (4),(4),(4),(4),(4),(3),(3);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);


MY PROPOSED QUERY EXECUTED WITH MORE DATA

mysql&gt; SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT DISTINCT num FROM
    -&gt; (
    -&gt;     SELECT grp,num,MAX(samecount) same FROM
    -&gt;     (
    -&gt;         SELECT
    -&gt;             (@group := @group + IF(@curnum=num,0,1)) grp,
    -&gt;             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    -&gt;             (@curnum:=num),num
    -&gt;         FROM logs ORDER BY id
    -&gt;     ) AA GROUP BY grp,num HAVING same &gt;= 3
    -&gt; ) A;
+-----+
| num |
+-----+
|   1 |
|   7 |
|  11 |
|   4 |
+-----+
4 rows in set (0.00 sec)

mysql&gt;


GIVE IT A TRY !!!