README_EN.md

180. Consecutive Numbers

中文文档

Description

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column.

 

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.

Solutions

SQL

# Write your MySQL query statement below
WITH
    t AS (
        SELECT
            *,
            CASE
                WHEN (lag(num) OVER (ORDER BY id)) = num THEN 0
                ELSE 1
            END AS mark
        FROM Logs
    ),
    p AS (SELECT num, sum(mark) OVER (ORDER BY id) AS gid FROM t)
SELECT DISTINCT num AS ConsecutiveNums
FROM p
GROUP BY gid
HAVING count(1) >= 3;