Today’s leetcode challenge was very challenging. It’s frustrating to not be able to answer at the same time not easily be able to understand other sucessful submissions.

Given a table, I had to find out the numbers that were repeated three times consecutively.

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

Expected Output:

ConsecutiveNums
1

Solution 1:

 SELECT DISTINCT Num AS ConsecutiveNums
 FROM (
     SELECT Id, Num
         , lead(ID, 2) OVER (PARTITION BY Num ORDER BY id) AS id2
     FROM Logs
 ) a
 WHERE a.id2 = a.id + 2 

To understand this solution, we would need to understand the ‘LEAD()’ function. The LEAD function is an analytic function that lets you query more than one rows and return value from the next nth rows of the table.

Syntax:

 LEAD ( expression [, offset [, default] ] )
 OVER ( [ query_partition_clause ] order_by_clause ) 

Breaking down the query:

I used the Live SQL freely available from Oracle to test out the subqueries. First I built a temporary table with the ‘WITH’ function as follows.

WITH
   Logs as (
     select 1 as Id, 1 as Num    from dual union all
     select 2      , 1           from dual union all
     select 3      , 1           from dual union all
     select 4      , 2           from dual union all
     select 5      , 1           from dual union all
     select 6      , 2           from dual union all
     select 7      , 2           from dual 
 ) 

Query 1:

 SELECT Id, Num, LEAD(ID, 2) OVER (PARTITION BY Num ORDER BY Id) AS id2
     FROM Logs; 

Output:

ID	Num	ID2
1	1	3
2	1	5
3	1	–
5	1	–
4	2	7
6	2	–
7	2	–

The query first partitions the table by ‘Num’ meaning that the table is grouped by the same values in Num. The partitions is then ordered by ID no. in ascending order. After which the ‘LEAD(ID, 2)’ function returns the ID no of the row after offsetting 2 rows.

Query 2:

 SELECT DISTINCT Num AS ConsecutiveNums
 FROM (
     SELECT Id, Num
         , lead(ID, 2) OVER (PARTITION BY Num ORDER BY id) AS id2
     FROM Logs
 ) a
 WHERE a.id2 = a.id + 2 

The outer ‘SELECT’ and ‘WHERE’ function then returns only those rows if the ID no. + 2 matches the ID2.

Solution 2:

SELECT DISTINCT Num AS "ConsecutiveNums" FROM
(
SELECT Num, LAG(Num) OVER (ORDER BY Id) AS prev,
LEAD(Num) OVER (ORDER BY Id) AS next FROM Logs
)
WHERE Num=prev AND Num=next;

Just like the ‘LEAD()’, ‘LAG()’ is another analytic function that queries more than one row in a table but retuns the previous nth rows in a table.

The output of the query selecting all the columns would look as below.

Id	Num	prev	next
1	1	–	1
2	1	1	1
3	1	1	2
4	2	1	1
5	1	2	2
6	2	1	2
7	2	2	–

With the ‘WHERE Num=prev AND Num=next’ syntax, only 1 row fulfils the condition which is the second row as the Num ‘1’ has 1 in the prev row and 1 in the next row.

• Facebook
• Instagram
• LinkedIn
• GitHub