LeetCode Curated SQL Solutions and Discussion - Week 3 | Part 1

In the SQL round of Data Engineering interviews, There is one very favourite type of question i.e. finding consecutive. In this third week, we will be discussing two very simple and interesting SQL problems from Leetcode. So let's start

Let's suppose we are having one table named CINEMA.

Table:?Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+

seat_id is an auto-increment primary key column for this table.
Each row of this table indicates whether the ith seat is free or not. 1 means free while 0 means occupied.

        

Now let's suppose we have to write a query to find all the consecutive available seats in the cinema. Let's look at one example for a better understanding of this question.

Input: 
Cinema table:
+---------+------+
| seat_id | free |
+---------+------+
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
+---------+------+

Output: 
+---------+
| seat_id |
+---------+
| 3       |
| 4       |
| 5       |
+---------+
        

There is a rule of thumb for solving these types of problems which involve finding some consecutive things and i.e. applying join within the table itself.

Here we need to find consecutive free seats right. Let's try to break down our solution

• Apply inner join between two tables i.e. CINEMA as c1 & CINEMA as c2
• Our joining condition will be abs(c1.seat_id - c2.seat_id) = 1. This particular inner join will create a table something like

seat_id, free, seat_id, fre
	1? ? ?1? ? ? 2? ? ? ?0
	2 	? 0? ? ? 1? ? ? ?1
	2? ? ?0? ? ? 3? ? ? ?1
	3? ? ?1? ? ? 2? ? ? ?0
	3? ? ?1? ? ? 4? ? ? ?1
	4? ? ?1? ? ? 3? ? ? ?1
	4? ? ?1? ? ? 5? ? ? ?1
	5? ? ?1? ? ? 4? ? ? ?1        

• Once this is done, we will just filter the result by applying where c1.free = 1 and c2.free = c1.free

Let's look at the full query once for a better understanding

select
	distinct c1.seat_id
from cinema as c1?
inner join cinema as c2?
on abs(c1.seat_id - c2.seat_id) = 1?
where c1.free = 1 and c2.free = c1.free?
        

I hope you can understand the above solution. Let's proceed to the next question ie. to find all numbers that appear at least three times consecutively. Let's suppose we are having the following table

Table:?Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

id is the primary key for this table.
        

Let's look at one example input and output table too.

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.        

Now let's try to approach this problem. If you look it very carefully then this problem is actually same as our previous problem. Here we need to find at least three consecutive occurrence of number instead of just finding two consecutive free seats. So let's try to approach this problem by very similar approach i.e. joining table itself. Here we will apply join two times as we need to compare three consecutive occurrence. Let's look at query directly

select distinct
? ? a.num as ConsecutiveNums
from logs as a?
inner join logs b on a.id = b.id - 1 and a.num = b.num?
inner join logs c on a.id = c.id - 2 and a.num = c.num?
        

I hope one can easily understand it by applying the same concept i.e. discussed for the first question. Actually, this problem can easily be solved using LAG & LEAD window function too. I hope someone will able to solve this using LAG & LEAD and comment her or his solution in the comment section.

One can find links to all these solutions on my youtube channel. Please like and subscribe to my channel, if you have learned to form it. Please hit the bell icon to never miss the update from my?The Big Data Show?Youtube channel.

• First Question Youtube Link
• Second Question Youtube Link