find all numbers that appear at least three times consecutively | is there anyway to achieve this using window function?

Question

find all numbers that appear at least three times consecutively: I am looking for a query to achieve this result using a window function rather than making a self join

Logs table:

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

Result table:

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+ 

here is my query:

select distinct l1.num as ConsecutiveNums from logs l1
inner join logs l2 on l1.id = l2.id - 1
inner join logs l3 on l1.id = l3.id + 1
where l1.num = l2.num and l2.num = l3.num

but I am looking to use a window function instead

Answer 1

You can use lag() to get the value in the "previous" row and the row before that. If they're all equal to the value in the current row, you got a match. Consecutive equal numbers that are more than three would be matched more than one time like this. And a repetition of such a consecutive sequence would do so too. You can use DISTINCT to select them only once.

SELECT DISTINCT
       num
       FROM (SELECT num,
                    lag(num, 1) OVER (ORDER BY id) = num
                    AND lag(num, 2) OVER (ORDER BY id) = num c3
                    FROM logs) x
       WHERE c3;

db<>fiddle