SQL Medium difficulty practice question : Consecutive Numbers

180. Consecutive Numbers 

Hello All, I am back with another SQL leetcode solution. This time we are going one step ahead and practice question with medium difficulty.


SQL Schema

Table: Logs

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

 

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.

 

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

Solution :

# Write your MySQL query statement below
SELECT Distinct L1.num as ConsecutiveNums
FROM Logs L1,
           Logs L2,
           Logs L3
WHERE L1.id = L2.id - 1
and   L2.id = L3.id - 1 
and   L1.num = L2.num
and   L2.num = L3.num


Explanation : 
First we will create 3 objects to represent three different entities in SQL query as L1, L2, L3.

Then, if id of L1 is 1, L2 is 2, L3 is 3 and so on. To refer L1,L2,L3 has 3 consecutive set of ids we will use WHERE clause as follow :

WHERE L1.id = L2.id - 1
and   L2.id = L3.id - 1 

The next condition to satisfy is num represented by L1, L2 and L3 is equal, which further shown by AND statement as follow :

and   L1.num = L2.num
and   L2.num = L3.num

Happy Learning !
For any queries feel free to comment !

Comments

Post a Comment

Popular posts from this blog

SQL for beginners : finding out Employees Earning More Than Their Managers

Leetcode question no. 181   181 .  Employees Earning More Than Their Managers SQL Schema Table:  Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.   Write an SQL query to find the employees who earn more than their managers. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Employee table: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ Output: ...
Read more

Answer to SQL Leetcode Question 183. Customers Who Never Order

Answer to SQL Leetcode Question 183 183 .  Customers Who Never Order   SQL Schema Table:  Customers +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID and name of a customer.   Table:  Orders +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ id is the primary key column for this table. customerId is a foreign key of the ID from the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who ordered it.   Write an SQL query to report all customers who never order anything. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Customers table: +----+-------+ | id | name | +---...
Read more