SQL Interview Question Asked in Tredence Analytics
HTML-код
- Опубликовано: 16 окт 2024
- 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...
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
Please like the video for more interview questions 🙏
@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?
@ankitbansal your website namaste sql is not working could you please look into it i tried lot but getting 505 server error
WITH t1 as(select *,
(seat_id - ROW_NUMBER() OVER ( order by seat_id)) as rnk
FROM
cinema
WHERE
free = 1),
t2 AS (SELECT *, count(*) over(Partition by rnk) as cons_records From t1)
SELECT seat_id from t2 where cons_records >=2;
u r superb like virendra sehwag...har ball pe chauka and 6😀
with cte as (SELECT *,lead(free,1) over(ORDER by seat_id asc) as ld,lag(free,1,0) over(ORDER by seat_id asc) as lg from cinema)
SELECT * from cte where free =1 and (ld=1 or lg=1)
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)
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;
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
This is the Simplest and Smallest query.
select seat_id from
(select seat_id, free, lead(free,1,1) over(order by seat_id) as lead_free from seats)
where free = 1 and free = lead_free;
WITH cte AS
(SELECT *,
LAG(free)OVER(ORDER BY seat_id) as prev,
LEAD(free)OVER(ORDER BY seat_id) as next
FROM cinema)
SELECT seat_id
FROM cte
WHERE free = 1
AND (prev = 1 or next = 1)
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
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.
Coudl you please add the DDL for the question
Description box
Grand explanation
method 2 is giving even two consecutive seats
We can also do this by WITH ConsecutiveSeats AS (
SELECT seat_id, free,
seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS group_id
FROM cinema
WHERE free = 1
),
ConsecutiveGroups AS (
SELECT group_id,
MIN(seat_id) AS start_seat,
MAX(seat_id) AS end_seat,
COUNT(*) AS seat_count
FROM ConsecutiveSeats
GROUP BY group_id
)
SELECT start_seat, end_seat, seat_count
FROM ConsecutiveGroups
WHERE seat_count > 2
ORDER BY start_seat;
A humble request to u plj thoda thoda hindi bhi bola kro by which i can understand easily
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
;
---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
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;
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)
My Approach:
with seat_rank as
(
select seat_id,free, (seat_id-row_number() over (partition by free order by seat_id)) as diff from cinema
),
Consecutive_seat as
(
select min(seat_id) start_seat,max(seat_id) end_seat,COUNT(seat_id) as consec
from seat_rank where free = 1 group by diff having COUNT(seat_id)=3
)
select seat_id from cinema c join Consecutive_seat f on c.seat_id between f.start_seat and f.end_seat
I was only strong in Power Bi before only but after watching and solving ur problems I have got interest in SQL
select seat_id from (
select *,
case when free='1' and(
lag(free,1) over(order by seat_id) ='1' or
lead(free,1) over(order by seat_id)='1') then 'Y'
else 'N'
end as flag
from cinema) a where flag='Y'
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
;
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
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
It will fail if free =0 for consecutive seats either before or after
@@abhiksaha3451 thanks for the correction.. edited it
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
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
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'
Hi Ankit,
with cte as(
select *,
lag(free,1) over(order by seat_id) as prev_seat,
lead(free,1) over(order by seat_id) as next_seat
from cinema)
select seat_id from cte
where (free=1 and prev_seat=1) or (free=1 and next_seat=1)
order by seat_id;
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;
WITH cte AS (
SELECT seat_id, free,
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
WHERE diff IN (
SELECT DISTINCT diff
FROM cte
GROUP BY diff
HAVING COUNT(*) > 1
)
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)
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'
This is an amazing approach, thanks. Very easy to understand.
Hey Ankit, what YOE was this question asked for? If u have any idea
This can be solved with self join, row number method, lead/lag and also advanced aggregation
Can't believe I used method 3 in my first attempt. Watchin your videos has really helped me pick your brain. Thank you Ankit!
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.
Sir please create a video on TIME function if possible.
Awesome ❤
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''
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);
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
Thanks sir 🙏
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
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
Sir it's for fresher interview question or experience one
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)
Thanks for sharing such questions ❤
Please try do more such videos. 👍
Good 👍👍👍👍❤❤
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)
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
At 12:17 using c2.seat_id - c1.seat_id = 1 also gives the same output
Hi Ankit,
Below is my approach :-
with cteseat as
(
select * , lead (free ) over ( order by seat_id) as next_seat
from cinema
)
,ctenext as
(
select seat_id , seat_id +1 as nextseatid
from cteseat where free = 1
and free = next_seat
)
select seat_id from ctenext
UNION
select nextseatid from ctenext
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
I think the best method was the third.
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...
WITH cte AS (
SELECT seat_id, free, seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS rn
FROM cinema
WHERE free=1
)
SELECT seat_id
FROM cte
WHERE rn IN (SELECT rn FROM cte GROUP BY rn HAVING COUNT(*)>=3)
Bhai video mai clarify rakho naa mere just specs waale ko stress aata hai
Change the video quality
same Leetcode question too
cool question!! channel is treasure of learning!!
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);