How to Write Advance SQL Queries | Consecutive Empty Seats | SQL Interview Questions
HTML-код
- Опубликовано: 24 июл 2024
- In this Video I will be solving a SQL problem with 3 different methods.
00:00 Understand the Problem
01:21 Method 1 : Lead/Lag
06:24 Method 2 : Advance Aggregation
10:55 Method 3: Using ROW_NUMBER
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Data Analyst Interview 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_...
Script:
create table bms (seat_no int ,is_empty varchar(10));
insert into bms values
(1,'N')
,(2,'Y')
,(3,'N')
,(4,'Y')
,(5,'Y')
,(6,'Y')
,(7,'N')
,(8,'Y')
,(9,'Y')
,(10,'Y')
,(11,'Y')
,(12,'N')
,(13,'Y')
,(14,'Y');
#sql #advance #dataengineer 
Наука
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
Too Good Ankit. Enlightening and Easy to Grasp.
Solved toe leet code problems #180 and #601 using the Lead,Lag approach as mentioned in the video . Thanks all your videos are very helpful if one wants to learn advanced SQL
Awesome 👍
I believe out of 3 solutions, 3rd solution can be applied to other scenarios like 4 or more consecutives with very minimal code changes. Good to know other solutions as well.
next level of concepts clearing..thank you so much sir.. maja aa gaya ye video dekh kar..
Maze aana bahut important hai 😍
i wish i had watcvhed your videos before attending latham and watkins he asked about window functions and cte too
thank you so much sir for valuable content ! 🙌
Thanks a ton Ankit . I have done in little more extensive way but your approach is something more catchy (method#3) --
===== Method 1 ==========
with cte as
(select seat_no,
lag(seat_no) over ( order by seat_no) as prev_seat_no,
lead(seat_no) over ( order by seat_no) as next_seat_no,
is_empty as current_seat,
lag(is_empty) over ( order by seat_no) as prev_seat,
lead(is_empty) over ( order by seat_no) as next_seat
from bookmyshow ),
cte2 as (
Select prev_seat_no,seat_no,next_seat_no from cte where prev_seat='Y' and current_seat='Y' and next_seat='Y') // Since the requirement is in one single column but unknowingly this too be question
Select distinct prev_seat_no as seat_no from cte2
union
Select distinct seat_no as seat_no from cte2
union
Select distinct next_seat_no as seat_no from cte2
-- ===== Method 2 ==========
with method2 as (
Select * ,
sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between 2 preceding and current row) as prev_row2,
sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between current row and 2 following ) as next_row2,
sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between 1 preceding and 1 following ) as current_row
from bookmyshow)
Select seat_no from method2 where 3 in ( prev_row2,current_row,next_row2)
Thanks for posting. Great effort 😊
Method 3 is so elegant.
as usual, the greatest.
You are really awesome!!, your way of explaining makes every logic clear!! Keep making such videos so we can learn more from this Videos
Thank you so much 😀
last one was so cool!
;with cte as
(
select *,ROW_NUMBER()over(order by seat_no) as rw,seat_no-ROW_NUMBER()over(order by seat_no) as grp from bms
where is_empty='Y'
)
select seat_no from cte where grp in(
select grp from cte
group by grp having count(1)>=3)
Just awesome...
very good explanation
Very informative, will certainly help in next interview
Thanks bro 😊
DAMN, i would have never thought of the second method
Great questions as always.
I did this using CTE and self joins:
WITH empty_neighbors AS (
SELECT a.*
FROM bms a
JOIN bms b ON a.seat_no - 1 = b.seat_no
JOIN bms c ON a.seat_no + 1 = c.seat_no
WHERE a.is_empty = 'Y'
AND b.is_empty = 'Y'
AND c.is_empty = 'Y'
)
SELECT DISTINCT m.seat_no, m.is_empty
FROM bms m
JOIN empty_neighbors n
ON m.seat_no = n.seat_no - 1
OR m.seat_no = n.seat_no + 1
OR m.seat_no = n.seat_no
ORDER BY m.seat_no;
Thanks for video ankit... 🙏
One request if you get time please make videos on python also.
Sure. I will plan the same.
I think method 1 and method 3 are the one that can be think of, 2nd approach requires more understanding of functions.
My output is different but will this suffice?
with cte as(
select *,
case when is_empty = "Y" and lead(is_empty,1) over() = "Y" and lead(is_empty, 2) over() = "Y" then 1 else 0 end as status
from bms)
select seat_no as first_seat,seat_no+1 as sec_seat, seat_no+2 as third_seat from cte where status = 1;
That's great Ankit. From the 3rd solution, what if the seat_no is not numeric and it contains something like A,B,G?
Do we have to generate the row_number twice in this case before and after filter?
Very interesting and nicely explained.
Thank you 😊
Hi Ankit, with lead lag, i also checked for the middle row, if the lag and lead is_empty are also the same
with cte as (
select seat_no, is_empty,
case
when
is_empty = lead(is_empty)over(order by seat_no) and
is_empty = lead(is_empty,2)over(order by seat_no) then 1
when
is_empty = lag(is_empty)over(order by seat_no) and
is_empty = lead(is_empty)over(order by seat_no) then 1
when
is_empty = lag(is_empty)over(order by seat_no) and
is_empty = lag(is_empty,2)over(order by seat_no) then 1 else 0
end as empty_flag
from bms)
select seat_no from cte where empty_flag = 1
Problem is it will give consecutive 3 or more non empty chairs also
Yes, works fine. Just add another filter condition : is_empty='Y' in the end
Thank you so much Sir🙏
Most welcome
When it comes to oracle , method 1 and 3 would be sufficient. Method 2 has some functional limitations and it would be working fine in SSMS. Over all this is good one dude
I think should work in Oracle also method 2. What limitations are there ?
@@ankitbansal6 do we have any preceding functions in Oracle. I hope we have some thing like unbound preceding and unbound following in the Nth value function.
@@prakashkrishnan7132 Unbound is like running sum. It's there in SQL server also. I think this will work in Oracle.
Yes, it works fine in oracle also.
hi Ankit Sir,
below solution gives correct answer, can we do like this?
select * from
(select * ,
count(is_empty) over (order by seat_no rows between 2 preceding and 2 following) counter
from bms) sub
where is_empty = 'Y' and counter = 5
with cte as (select * from bms where is_empty = 'Y'),
cte2 as (select *, seat_no - row_number() over(order by seat_no) as cnt from cte order by seat_no)
select seat_no from cte2 where cnt in (select cnt from cte2 group by 1 having count(*) >=3);
with ctl as (select seat_no,is_empty,is_empty_no,
sum(is_empty_no) over(order by seat_no rows between 2 preceding and current row) as case_1,
sum(is_empty_no) over(order by seat_no rows between 1 preceding and 1 following) as case_2,
sum(is_empty_no) over(order by seat_no rows between current row and 2 following) as case_3
from
(select a.*,
case when is_empty='Y' then 1 else 0 end as is_empty_no
from bms a) as A)
select distinct seat_no from ctl
where case_1=3 or case_2=3 or case_3=3;
Sir my solution:
with cte as
(
select *,ROW_NUMBER() over(order by seat_no) as se, seat_no -ROW_NUMBER() over(order by seat_no) as so
from bms
where is_empty = 'Y'
)
,cteone as
(
select seat_no,count(so) over(partition by so order by so) as c from cte
)
select seat_no from cteone
where c>=3
with cte as (
select *,sum (case when is_empty='N' then 1 else 0 end )
over (order by seat_no ) as k
from bms
),cte2 as (select k,count(seat_no) as cnt
from cte
where is_empty='y'
group by k )--select * from cte2
select seat_no from cte join cte2 on cte.k=cte2.k
where is_empty='y' and cnt>=3
my approach :
with cte as(
select *,seat_no - row_number() over(partition by is_empty) as diff from bms where is_empty = 'Y'),
cte2 as(
select *,count(1) over(partition by diff) as rpt from cte)
select seat_no from cte2 where rpt >= 3;
i had a doubt for this approach. Wouldnt a case where non consecutive rpt >3 could be selected ? its not applicable in this case but generally asking
@@himadrivaja3081 i didn’t get your question.Why would it be selected?
Good one
Thank you 😊
with cte as(
select seat_no from bms where is_empty between 'Y' and 'Y' )
,cte1 as(
select *,ROW_NUMBER() OVER (ORDER BY seat_no) as ID from cte)
,cte2 as(
select seat_no,(seat_no-Id) as rank from cte1)
,cte3 as(
select rank from cte2 group by rank having count(rank)>=3)
select seat_no from cte2 c2 inner join cte3 c3 on c2.rank=c3.rank
Superb
select seat_no from
(
select * , count(*) over(partition by rt ) as ct from
(
select * , (seat_no - row_number() over (partition by is_empty order by seat_no)) as rt from bms
where is_empty = 'Y') )where ct >= 3 order by 1
with temp as (
Select *,
seat_no - row_number()over(order by seat_no) as diff
from bms
where is_empty = 'Y'
)
Select seat_no,
count(diff)over(partition by diff) as dk
from temp
qualify dk >=3;
Hi Ankit Sir, Can you please give a suggestion on that?
with first_cte as (
select a.* from
(
select * , lag(is_empty,1," ") over (order by seat_no) as lag_is_empty ,
lead(is_empty,1," ") over (order by seat_no) as lead_is_empty
from bms
) as a where is_empty = 'Y' and lag_is_empty = 'Y' and lead_is_empty = 'Y'
)
select distinct seat_no from
(
select a.prev_seat as seat_no from
(select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a
inner join bms as b on a.prev_seat = b.seat_no
union all
select a.seat_no from
(select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a
inner join bms as c on a.seat_no = c.seat_no
union all
select a.next_seat as seat_no from
(select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a
inner join bms as d on a.next_seat = d.seat_no
) as a order by a.seat_no ;
Looks good. Did you try running it ?
Hi Ankit Sir I ran it. It is giving the output.
@@LiveWithDebasmita Awesome
/* done brother */
/* 3 or more consective empty seats */
with cte as (
select *,
lag(is_empty,1) over (order by seat_no) as prev1,
lag(is_empty,2) over (order by seat_no) as prev2,
lead(is_empty,1) over (order by seat_no) as nxt1,
lead(is_empty,2) over (order by seat_no) as nxt2
from bms )
select seat_no from cte
where is_empty='Y' and prev1='Y' and prev2='Y'
or
is_empty='Y' and prev1='Y' and nxt1='Y'
or
is_empty='Y' and nxt1='Y' and nxt2='Y'
Had I watched your videos early I could have cleared Amazon interview, I thought no one teaches sql so well as all the videos were related to coding on youtube.
Better late than never 😊
Will method 2 work on mysql and postgres?
Not sure. You can check functions .
SELECT seat_no
from(
SELECT *,
sum(case when is_empty ="Y" then 1 else 0 END) over (order by seat_no rows between 2 preceding and current row) as prev_2,
sum(case when is_empty ="Y" then 1 else 0 END) over (order by seat_no rows between 1 preceding and 1 following) as curr,
sum(case when is_empty ="Y" then 1 else 0 END ) over (order by seat_no rows between current row and 2 following ) as next_2
from bms) tmp
where prev_2 = 3 or curr =3 or next_2 =3
select seat_no from (select * ,count(d) over(partition by d) as v from (select seat_no,is_empty,seat_no-rank() over(partition by is_empty order by seat_no)
as d from bms)p
where is_empty='Y')j
where v>=3
2 approaches using MySQL
-- approach 1 using lead function
WITH cte2 AS(
WITH cte AS (
SELECT
*,
LEAD(is_empty) OVER() AS lead_1
FROM
bms
)
SELECT
*,
LEAD(lead_1) OVER() AS lead_2
FROM
cte
)
SELECT
seat_no,
seat_no+1,
seat_no+2
FROM
cte2
WHERE
is_empty = 'Y' AND
lead_1 = 'Y' AND
lead_2 = 'Y'
;
-- using window functions
WITH cte AS (
SELECT
is_empty,
seat_no,
ROW_NUMBER() OVER(PARTITION BY is_empty) AS rn
FROM
bms
)
SELECT
seat_no
FROM
cte
WHERE
ABS(seat_no-rn) IN (3,2)
;
Please increase the font size
Done for recent videos
Another Simpler way by just Using Lags & NO LEADS & Joins needed
***********************************************************************
WITH cte as (
select *
, CASE WHEN is_empty = 'Y' then 1 END as RNK
, LAG(is_empty,3)over(order by CASE WHEN is_empty = 'Y' then 1 END) as LAGS
from bms )
select seat_no, LAGS
from cte
where LAGS = 'Y'
order by seat_no
Thanks Ankit. Here is my approach to this problem
with
cte
as
(
SELECT *
, LEAD(seat_no, 2) over (order by seat_no) as lead_seat_no
from bms
where is_empty = 'Y'
)
,
cte2
as
(
(select seat_no, lead_seat_no
from cte
where lead_seat_no - seat_no = 2
)
)
select distinct b.seat_no
from bms b
join cte2
on b.seat_no between cte2.seat_no and cte2.lead_seat_no
with cte as (
select a.seat_no a_seat_no, b.seat_no b_seat_no,c.seat_no c_seat_no,
a.is_empty a_is_empty,b.is_empty b_is_empty,c.is_empty c_is_empty
from bms a inner join bms b on a.seat_no + 1 = b.seat_no
inner join bms c on a.seat_no + 2 = c.seat_no
where a.is_empty = b.is_empty and a.is_empty = c.is_empty
and a.is_empty = 'Y' and b.is_empty = 'Y' and c.is_empty = 'Y')
select a_seat_no, a_is_empty from cte
union
select b_seat_no, b_is_empty from cte
union
select c_seat_no, c_is_empty from cte
WITH cte1 AS (
SELECT seat_no, SUM(CASE WHEN is_empty = 'N' AND next_is_empty = 'Y' THEN 1 WHEN is_empty = 'Y' AND next_is_empty = 'N' THEN 1 ELSE 0 END) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sumover FROM
(
SELECT seat_no, is_empty, LAG(is_empty, 1, is_empty) OVER () AS next_is_empty
FROM bms
) z
)
, cte2 AS (
SELECT sumover
FROM cte1
GROUP BY sumover
HAVING COUNT(seat_no) >= 3
)
SELECT seat_no FROM cte1 WHERE sumover IN (SELECT * FROM cte2)
;