In this tutorial, we are going to write SQL Queries based on two scenarios of the problem - How many times a number occurs consecutively?
The detailed explanation is provided in the below YouTube video tutorial -
Please create the below Logs table and insert the data -
Create table Logs
( id integer identity,
num varchar(50)
);
INSERT INTO Logs VALUES ('1');
INSERT INTO Logs VALUES ('1');
INSERT INTO Logs VALUES ('1');
INSERT INTO Logs VALUES ('2');
INSERT INTO Logs VALUES ('1');
INSERT INTO Logs VALUES ('2');
INSERT INTO Logs VALUES ('2');
SQL Query - 1
Leetcode - Find numbers that occur 3 times consecutively in Logs table -
Leetcode query link -
With Numbers as
(Select
ID,
Num,
LAG(NUM) OVER (ORDER BY ID) as LAG_Num,
LEAD(NUM) OVER (ORDER BY ID) as LEAD_NUM
FROM
LOGS)
Select Num from Numbers
WHERE Num = Lag_Num
AND Num = Lead_Num;
SQL Query - 2
Find how many times a number occurs consecutively in the Logs table -
With CS_NUMS as
(Select ID, Num,
--row_number() OVER (ORDER BY ID) as RN_ID,
--row_number() OVER (PARTITION BY NUM ORDER BY ID) as RN_Num,
(row_number() OVER (ORDER BY ID) -
row_number() OVER (PARTITION BY NUM ORDER BY ID) ) as FN_GRP
from Logs )
Select Num, Count(*)
from CS_NUMS
Group BY FN_GRP, NUM