SQL Interview Question Asked in Tredence Analytics
HTML-код
- Опубликовано: 10 фев 2025
- 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
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.
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.
proud, solved using method 3 in first attempt.
All thanks to you!
Excellent!
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, seat_id - rn as diff from
(select *, row_number() over() rn from
(select seat_id from cinema where free = 1)a)l
where seat_id - rn in
(select diff from
(select seat_id, seat_id - rn as diff from
(select *, row_number() over() rn from
(select seat_id from cinema where free = 1)a)f)l
group by diff
having count(diff) >= 2)
Thanks, Ankit!
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...
Thanks for sharing such questions ❤
Please try do more such videos. 👍
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
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
u r superb like virendra sehwag...har ball pe chauka and 6😀
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;
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;
---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
select seat_id from (select
case when lead(free)over(order by seat_id)=1 and free=1 then seat_id
when free=1 and lag(free)over(order by seat_id)=1 then seat_id
end as seat_id
from cinema) abc
where seat_id is not null
Grand explanation
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)
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
with cte as (
select *
, sum(case when free = 0 then 1 else 0 end) over (order by seat_id) as gr_id
from cinema ),
cte2 as (
select * , count(*) over (partition by gr_id) as seq_count from cte )
select * from cte2 where seq_count-1>=2 and free !=0;
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,
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
Hi Ankit,
Here's my take on the problem:
with cte as (
SELECT * FROM cinema WHERE Free=1)
select a.seat_id from cte a join cte b on a.seat_id=b.seat_id+1
union
select a.seat_id from cte a join cte b
with cte_preceding as(
select *,sum(if(free=1,null,1))
over(order by seat_id rows between unbounded preceding and
current row) sm from cinema
)
select * from(
select *,sum(1)
over(partition by free,sm) rnk
from cte_preceding order by seat_id) x where rnk>=2
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
;
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
Awesome ❤
cool question!! channel is treasure of learning!!
Good 👍👍👍👍❤❤
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'
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''
The following is my approach:
select distinct a.seat_id
from cinema a join cinema b on abs(a.seat_id - b.seat_id) = 1 and a.free = 1 and b.free = 1
order by a.seat_id;
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)
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
Thanks sir 🙏
At 12:17 using c2.seat_id - c1.seat_id = 1 also gives the same output
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
Solution :
with cte as (
select *,
lead(seat_id) over( order by seat_id) as nextseat_id,
lead(free) over( order by seat_id) as nextfree
from cinema
),
cte2 as (
select * from cte where free - nextfree = 0
)
select seat_id from cte2
union
select nextseat_id from cte2 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 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)
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 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)
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'
This can be solved with self join, row number method, lead/lag and also advanced aggregation
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
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;
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 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;
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;
I think the best method was the third.
same Leetcode question too
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
A humble request to u plj thoda thoda hindi bhi bola kro by which i can understand easily
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);
sir can you share what sql questions were asked in the online assessment of tredence analytics
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
Sir please create a video on TIME function if possible.
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)
Sir it's for fresher interview question or experience one
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;
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.
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
WITH cte AS(SELECT *, seat_id-flag AS ff FROM (SELECT *, ROW_NUMBER() over(ORDER BY seat_id) AS flag FROM cinema WHERE FREE=1))
SELECT seat_id,cnt FROM(SELECT *,count(*) over(PARTITION BY ff ) AS cnt FROM cte ) WHERE cnt>=2
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
;
Coudl you please add the DDL for the question
Description box
Hey Ankit, what YOE was this question asked for? If u have any idea
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 *, 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
WITH cte AS(SELECT *,row_number() over(PARTITION BY FREE ORDER BY seat_id) AS flag FROM cinema WHERE FREE!=0 ORDER BY seat_id),
cte2 AS(SELECT *,count(flag1) OVER(PARTITION BY flag1 ) AS cnt FROM (SELECT *,CASE WHEN FREE=0 THEN 0 ELSE seat_id-flag END AS flag1 FROM
cte))
SELECT seat_id FROM cte2 WHERE cnt>1
Bhai video mai clarify rakho naa mere just specs waale ko stress aata hai
Change the video quality
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)
Leetcode ka s h
SELECT * FROM
(SELECT case when cnt is not null then cnt ELSE cnt2 END AS final FROM
(SELECT *,CASE WHEN free=lagg theN seat_id ELSE null END AS cnt ,
CASE WHEN free=leadd theN seat_id ELSE null END AS cnt2
FROM
(
SELECT * , lag(free) over(ORDER by seat_id) as lagg,
lead(free) over(ORDER by seat_id) as leadd
FROM cinema)
))
WHERE final is not null
@ankitbansal6 in coding question is python language is compulsory?
from pyspark.sql.functions import *
from pyspark.sql.window import Window
window=Window.partitionBy(col("free")).orderBy(col("seat_id"))
free=cinema_df.withColumn("rn",row_number().over(window)).filter(col("free")==1)
f=free.withColumn("flag",col("seat_id")-col("rn"))
res=f.withColumn("cnt",count("*").over(Window.partitionBy(col("flag")))).filter(col("cnt")>1).orderBy(col("seat_id"))
res.show()