Leetcode: Consecutive Numbers

 

This question is asked at Amazon. It is as follows: 

Write a SQL query to find all numbers that appear at least 3 times consecutively. 

Here's the input:

And here's the output: 

In these type of questions, you should probably think of a self join or lag and lead function and look at the row before and after your current role, and self-joins you can look at your differential conditions. This is a great way of figuring out self-join and we want to find idea with 3 consecutive numbers, so we will self join twice.

We first select the number from the table Logs a. 

Then we start out by joining already. If we're self joining we're going to use the same table again, with a  table B, and we're going to join. We want to look at consecutive numbers so we're going to use Id to see which numbers ore consecutive. So we want a.id = b.id + 1 which means b.id is higher than a.id and as a result, we will get the first 2 rows. 

We also want to join on the number and make sure we have the same number, because our task is to find numbers that appear 3 times consecutively. 

Here's the solution for numbers appearing at least 2 times consecutively.

SELECT a.Num FROM Logs a

JOIN Logs b ON a.Id + b.Id + 1 AND a.Num = b.Num.

We just make it 3 times by joining logs again, calling that one c, which should be 2 higher than a, yielding the same number as well. In that case we established an Id a, with 1 higher than 1 and 2 higher than 1. We use the same patter for 4-joins. If we run the query, that should give accepted output, naming the output field as ConsecutiveNums. We use the DISTINCT keyword to only show the A array once. 

Here's the solution:

SELECT DISTINCT a.NUM AS ConsecutiveNums FROM Logs a

JOIN Logs b ON a.Id = b.Id + 1 AND a.Num = b.Num

JOIN Logs c ON a.Id = c.Id + 2 AND a.Num = c.Num