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 starting from 1.
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.
Solution
PostgreSQL
-- Write your PostgreSQL query statement below
select distinct l1.num as ConsecutiveNums
from logs l1
where exists (
select 1 from logs l2
where l2.id = l1.id + 1 and l2.num = l1.num
) and exists (
select 1 from logs l2
where l2.id = l1.id + 2 and l2.num = l1.num
);