Consecutive Numbers
Q
LAG
WITH t AS (
SELECT id,
num,
LAG(num,1,null) OVER() AS prev_num,
LAG(num,2,null) OVER() AS prev_prev_num
FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM t
WHERE (num = prev_num AND num = prev_prev_num)
JOIN (faster):
SELECT DISTINCT num AS ConsecutiveNums
FROM Logs l JOIN Logs ll on l.num = ll.num AND l.id - ll.id BETWEEN 1 AND 2
GROUP BY l.id,l.num
HAVING COUNT(DISTINCT ll.id) >= 2
WITH & LEAD
with t1 as(select num,num-lead(num,1) over(order by id) as first,num-lead(num,2) over(order by id) as second from Logs)
select distinct num as ConsecutiveNums
from t1
where first = 0 and second = 0
WHERE
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
