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) >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> DROP DATABASE IF EXISTS guru;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

mysql> 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> 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> SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) > 1;
+-----+
| Num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql>

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 >= 3
) A;

Looks messy, right ? Here is is executed

MY PROPOSED QUERY EXECUTED

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

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

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

mysql> 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 >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

mysql>

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> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> 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
    -> ;
+------+-----------+----------------+-----+
| 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>

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> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> 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 >= 3;
+------+-----+------+
| grp  | num | same |
+------+-----+------+
|    1 |   1 |    3 |
+------+-----+------+
1 row in set (0.00 sec)

mysql>

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 EX

StackExchange Database Administrators Q#89493, answer score: 4