SQL LeetCode: 180. Consecutive Numbers

Pallavi Mirajkar Dantkale
1 min readMar 30, 2021

--

180. Consecutive Numbers

Medium

SQL Schema

Table: Logs

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id is the primary key for this table.

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example:

Logs table:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 is the only number that appears consecutively for at least three times.
  1. Using Self join: We are going to self join Logs table.

SELECT
distinct l1.Num as ConsecutiveNums
FROM
Logs l1
join Logs l2
on l1.id = l2.id -1
and l1.num = l2.num
join Logs l3
on l2.id = l3.id -1
and l2.num = l3.num

2. Using window analytic function LEAD() :

WITH CTE AS (

SELECT
Num,
LEAD(Num,1) OVER (order by Id) as l1,
LEAD(Num, 2) OVER (order by Id) as l2
FROM
Logs )

SELECT distinct Num as ConsecutiveNums
FROM CTE
WHERE num = l1 and num=l2 ;

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

--

--

Pallavi Mirajkar Dantkale
Pallavi Mirajkar Dantkale

Written by Pallavi Mirajkar Dantkale

QA Engineer / Data Analyst — Highly committed to Quality Assurance and data analysis, advocate for quality and add the right value to the organization.

No responses yet

Write a response