SQL Interview Question Asked in Tredence Analytics

In this video we will solve a SQL interview question asked in Tredence Analytics . We will solve it using 3 methods.
High quality in depth Analytics courses : www.namastesql.com/
script:
CREATE TABLE cinema (
seat_id INT PRIMARY KEY,
free int
);
delete from cinema;
INSERT INTO cinema (seat_id, free) VALUES (1, 1);
INSERT INTO cinema (seat_id, free) VALUES (2, 0);
INSERT INTO cinema (seat_id, free) VALUES (3, 1);
INSERT INTO cinema (seat_id, free) VALUES (4, 1);
INSERT INTO cinema (seat_id, free) VALUES (5, 1);
INSERT INTO cinema (seat_id, free) VALUES (6, 0);
INSERT INTO cinema (seat_id, free) VALUES (7, 1);
INSERT INTO cinema (seat_id, free) VALUES (8, 1);
INSERT INTO cinema (seat_id, free) VALUES (9, 0);
INSERT INTO cinema (seat_id, free) VALUES (10, 1);
INSERT INTO cinema (seat_id, free) VALUES (11, 0);
INSERT INTO cinema (seat_id, free) VALUES (12, 1);
INSERT INTO cinema (seat_id, free) VALUES (13, 0);
INSERT INTO cinema (seat_id, free) VALUES (14, 1);
INSERT INTO cinema (seat_id, free) VALUES (15, 1);
INSERT INTO cinema (seat_id, free) VALUES (16, 0);
INSERT INTO cinema (seat_id, free) VALUES (17, 1);
INSERT INTO cinema (seat_id, free) VALUES (18, 1);
INSERT INTO cinema (seat_id, free) VALUES (19, 1);
INSERT INTO cinema (seat_id, free) VALUES (20, 1);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@ankitbansal6]

Please like the video for more interview questions 🙏

[@Sai-dc7lc]

@Ankit, It’s been a week since the sql video recordings are not opening. Even when I tried reloading the video, it’s not showing anything. Could you please look into it?

[@sukanyaiyer2671]

---Method 3---
select * from(
select * ,
lag(free,1) over (order by seat_id) as prev ,
lead(free,1) over (order by seat_id) as next
from cinema) as a
where free = prev or free = next

[@saralavasudevan5167]

Hi Ankit thanks for the question and your approaches! This is my take at the problem:
with mycte as
(
select *,
case when (free = 1 and prev_free = 1) or (free = 1 and next_free = 1) then 'Yes' else 'No' end as seat_status
from
(
select *,
lag(free,1) over(order by seat_id) as prev_free,
lead(free,1) over(order by seat_id) as next_free
from cinema
) as x
)
select seat_id from mycte
where seat_status = 'Yes'

[@Chathur732]

Hi Ankit, my approach is as follows:
with cte as (select seat_id , (seat_id - rn) as diff
from (
select * , rank() over(partition by free order by seat_id) as rn
from cinema C
where C.free = 1
order by C.seat_id) a
group by seat_id)
select seat_id
from cte
where diff in (SELECT diff
FROM cte
GROUP BY diff
HAVING COUNT(diff) >= 2)

[@Datapassenger_prashant]

there is always something to learn in each of your video sir.
I have solved such example a lot from your videos. Stil got to learn a different approach: self join approach to find consecutive seat.
Thanks a lot...

[@shubhamsoni174]

Thanks for sharing such questions ❤
Please try do more such videos. 👍

[@nirmalpatel3430]

cool question!! channel is treasure of learning!!

[@SumanSadhukhan-md4dq]

Awesome ❤

[@aswinc4829]

Grand explanation

[@vivekdutta7131]

with cte AS
(
select *,row_number() over(order by seat_id) as rn,
seat_id - row_number() over(order by seat_id) as diff
from cinema
where free = 1
)
select seat_id from cte JOIN
(select diff,count(*) as cnt
from cte
group by diff having count(*) >1
)A
on cte.diff = a.diff
order by seat_id

[@ankitsaxena565]

Thanks sir 🙏

[@Clintonlobo]

Can't believe I used method 3 in my first attempt. Watchin your videos has really helped me pick your brain. Thank you Ankit!

[@invincible9971]

Same here, I have also created the 3rd attempt logic at first time before watching the solution. This is my version: select * from
(select
case when free = lead(free) over (order by seat_id) or free = lag(free) over (order by seat_id)
then seat_id end as free_seats
from cinema ) c
where free_seats is not null ;
The videos really gives clarity for logic building in SQL.

[@VAR21723]

Good 👍👍👍👍❤❤

[@saiteja-gb8ho]

Thanks for posting such useful questions bro . Here is my approach in Postgres :
WITH CTE AS
(SELECT * ,ROW_NUMBER()OVER(ORDER BY SEAT_ID) AS RN
FROM CINEMA WHERE FREE=1)
SELECT SEAT_ID FROM CTE WHERE SEAT_ID -RN=1

[@tamojeetchatterjee9385]

Hi Ankit vey interesting question Here is my solution for this
with cte as (select free , seat_id , lag(seat_id)over() ,
seat_id - lag(seat_id)over() as diff
from cinema where free 0)
--select * from cte
, gte as (select * , lead(diff)over() as diff_2 from cte)
--select * from gte
select seat_id from gte where diff 2 or diff_2 2

[@sandeepanand3834]

In method 2, how you got output in ascending order; i am not getting the output in ascending order?
its union between 2 tables without sorting.

[@vaibhavverma1340]

with cte as(select *, seat_id-row_number() over (order by seat_id)rn
from cinema
where free = 1)
, cte2 as
(select *, count(*) over (partition by rn)cnt from cte)
select cte2.seat_id from cte2
where cnt>=2

[@abhishekgarg9029]

Hey Ankit, what YOE was this question asked for? If u have any idea

[@amritsinghania2722]

Sir it's for fresher interview question or experience one

[@adityakishan1]

with cte1 as (
select
seat_id,
free,
case when (free=1 and lead(free) over(order by seat_id) =1 ) then 'c'
when (free=1 and lead(free) over(order by seat_id) =0 and lag(free) over(order by seat_id) =1) then 'c'
when (free=1 and lead(free) over(order by seat_id) =0 and lag(free) over(order by seat_id) =0) then 'n'
else 'n' end flg
from
cinema
)
select seat_id from cte1 where flg='c'

[@bommanasravan8279]

with cte as(
select seat_id,free,case when free=1 and (lead(free)over(order by seat_id)=1 or lag(free)over(order by seat_id)=1) then 'yes'
else 'no' end as 'flag'
from cinema)
select seat_id,free from cte where flag='yes''

[@mrpam4672]

This can be solved with self join, row number method, lead/lag and also advanced aggregation

[@bhumikalalchandani321]

same Leetcode question too

[@user-dk4xj5ub9q]

WITH CTE AS(
SELECT seat_id, free, CASE WHEN free=1 THEN 'vacant' ELSE 'occupied' END AS availability,
LAG(free) OVER(ORDER BY seat_id) AS prev_seat ,
LEAD(free) OVER(ORDER BY seat_id) AS next_seat
FROM cinema)
SELECT seat_id
FROM CTE
WHERE free=1 AND (next_seat=1 OR prev_seat=1)
ORDER BY seat_id;
EASY approach

[@abhiksaha3451]

It will fail if free =0 for consecutive seats either before or after

[@user-dk4xj5ub9q]

@@abhiksaha3451 thanks for the correction.. edited it

[@dgsharma4773]

with cte as(
select *,
lag(free,1) over (order by seat_id) as prev_seat,
lead(free, 1, free) over (order by seat_id) as next_seat
from cinema
)
select
seat_id
from cte
where free = 1 and ( prev_seat = 1 and next_seat = 1) or
(prev_seat = 1 and next_seat= 0) or
(prev_seat = 0 and next_seat = 1)

[@KoushikT]

with A as (select
seat_id,
free,
seat_id - row_number() over (order by seat_id) as grp
from cinema
where free = 1)
select
seat_id
from
A
where grp in
(select grp from A group by 1 having count(*)>2)

[@Vaibha293]

with cte as(
select * ,seat_id-sum(free) over(order by seat_id)t
from cinema
where free = 1)
select seat_id
from cte
where t in (select t
from cte
group by t
having count(t)>=2)

[@user-dw4zx2rn9v]

MySQL solution: with cte as (select seat_id, diff, count(*) over (partition by diff) as cnt from (
select *, row_number() over (order by seat_id) as rw, seat_id - row_number() over (order by seat_id) as diff from cinema
where free = 1
) as x
)
select seat_id from cte
where cnt >= 2

[@hariikrishnan]

At 12:17 using c2.seat_id - c1.seat_id = 1 also gives the same output

[@ashwingupta4765]

with cte as (
select *,
(lag(free,1) over( order by seat_id)*free) as lde,
(lead(free,1) over( order by seat_id)*free) as rwn
from cinema)
Select * from cte
where lde =1 or rwn =1

[@gopinathg318]

My solution with small differences
--Method 1 using lead and lag
with free_seats as(
select seat_id, lead(seat_id)over(order by seat_id) as next_seat,lag(seat_id)over(order by seat_id) as previous_seat
from cinema
where free=1
)
select seat_id
from free_seats
where (seat_id+1=next_seat or seat_id-1=previous_seat)
;
--Method 2 using inner join
select distinct c1.seat_id
from cinema c1 inner join cinema c2
on (c1.seat_id+1=c2.seat_id or c1.seat_id-1=c2.seat_id)
where c1.free=1
and c2.free=1
order by c1.seat_id
;

[@gameply347]

Sir please create a video on TIME function if possible.

[@monasanthosh9208]

Select Seat_id,Free from
(Select *,Count(flag) over (Partition by Flag) as seg from
(Select *,seat_id-row_number() over (Order by seat_id) as Flag from
(Select * from cinema where free=1)N)N)N where Seg>2;

[@MuskanGoyal-db7cs]

with cte as(
select seat_id, free,lead(free,1)over(order by seat_id) as new
from cinema)
select seat_id ,free from cte where new=1 and free=1;

[@sumitshrivastava4494]

with cte as (
SELECT *,
seat_id - free as sub_seat_free
from cinema)
select seat_id, free
from (
SELECT *,
lag(sub_seat_free, 1) over(order by sub_seat_free) as previous_row,
lead(sub_seat_free, 1) over(order by sub_seat_free) as next_row
from cte ) a
where sub_seat_free - previous_row = 1 or next_row - sub_seat_free = 1

[@harshitsalecha221]

WITH cte1 AS (SELECT *,
LEAD(seat_id) OVER(ORDER BY seat_id)-seat_id as lead_seat_diff,
seat_id-LAG(seat_id) OVER(ORDER BY seat_id) as lag_seat_diff
FROM cinema
WHERE free=1)
SELECT seat_id
FROM cte1
WHERE lead_seat_diff=1 OR lag_seat_diff=1;

[@ashanair1602]

I think the best method was the third.

[@bukunmiadebanjo9684]

I used attempt 3 straight off the bat.
here is my attempt;
with cte as (SELECT c.*, LEAD(c.free,1,0) OVER(ORDER BY c.seat_id) after, LAG(c.free,1,0) OVER(ORDER BY c.seat_id) before
FROM cinema c)
SELECT seat_id
FROM cte
WHERE (free = 1 and after = 1) OR (free = 1 and after = 1 and before = 1) OR (free = 1 and before = 1)

[@Apna_tahlka_123]

A humble request to u plj thoda thoda hindi bhi bola kro by which i can understand easily

[@sravyasrinivas]

Coudl you please add the DDL for the question

[@ankitbansal6]

Description box

[@Savenature635]

Thanks for sharing this question,
Here is my approach :
with cte as (select *,row_number() over() as rn,
seat_id-row_number() over() as grp
from cinema where free=1)
select seat_id
from cte
where grp in (select grp from cte group by grp having count(1)>=2);

[@mohammaddanishkhan7288]

Here's my solution Sir, let me know what you think about it.
WITH flag_cte AS (
SELECT
*,
SUM(free) OVER(ORDER BY seat_id
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS lead_flag,
SUM(free) OVER(ORDER BY seat_id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS lag_flag
FROM cinema
)
SELECT
seat_id
FROM
flag_cte
WHERE
lead_flag = 2 OR lag_flag = 2

[@user-gg6dt9st8u]

method 2 is giving even two consecutive seats

[@shashwatdev2371]

Here is my approach using correlated subquery-
select seat_id
from cinema a
where exists (select 1
from cinema b
where a.free=1 and b.free=1 and (a.seat_id+1=b.seat_id or a.seat_id-1=b.seat_id))
another simpler method-
with cte as
(
select seat_id
from cinema
where free=1
)
select seat_id
from cte
where seat_id-1 in (select seat_id from cte) or seat_id+1 in (select seat_id from cte);

[@abhiksaha3451]

The 2nd solution will takes a lot of resources if you have millions of rows as "in" is equivalent to multiple "or"s and it has to run 3x

[@The5f5f5f5]

WITH T AS (
SELECT SEAT_ID,CASE WHEN (SEAT_ID-LAG(SEAT_ID) OVER() =1) OR (LEAD(SEAT_ID) OVER()-SEAT_ID = 1) THEN 1 ELSE 0
END AS SEQUENCE_FILTER
FROM CINEMA WHERE FREE=1)
SELECT SEAT_ID FROM T WHERE SEQUENCE_FILTER=1
;

[@user-rc2uc1kv6w]

Ankit sir aap please Full Microsoft SQL Server pe ek course launch karein 🤲 abhi k time MSSQL demand mein hain. usme SSMS, SSAS, SSRS ye bhi sikhain

[@hrishiAOL]

Bhai video mai clarify rakho naa mere just specs waale ko stress aata hai

[@ankitbansal6]

Change the video quality