Description

Table: Logs

Column NameType
idint
numvarchar
  • In SQL, id is the primary key for this table. id is an autoincrement column.

Problem Statement

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:

  • Logs table:
idnum
11
21
31
42
51
62
72

Output:

ConsecutiveNums
1

Explanation: 1 is the only number that appears consecutively for at least three times.

Solution

In this case, you need to find all numbers that appear at least three times consecutively. If it’s more than three times, you will still output that number only once because you need to find distinct numbers.

Approach 1: Using LAG, LEAD functions

This is where you can use LAG and LEAD functions to find previous and next number for current number. If these three numbers are same, then it means that they occurred three times consecutively. This problem didn’t explicitly mention about the order when finding consecutive numbers. So, you can assume that you have to find consecutive numbers in order of id. You could also use the default order without specifying ORDER BY in the window.

1
2WITH cte AS (
3    SELECT num, 
4        LEAD(num, 1) OVER (ORDER BY id) 'num_lead',
5        LAG(num, 1) OVER (ORDER BY id) 'num_lag'
6        FROM Logs
7) SELECT DISTINCT num ConsecutiveNums FROM cte
8    WHERE num = num_lead AND num = num_lag;