SQL Interview Question Asked in Tredence Analytics

[@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?

[@Vinaykumar-ny8rm]

@ankitbansal your website namaste sql is not working could you please look into it i tried lot but getting 505 server error

[@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.

[@suyashgupta6169]

proud, solved using method 3 in first attempt. All thanks to you!

[@ankitbansal6]

Excellent!

[@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'

[@thatwasavailable]

This is an amazing approach, thanks. Very easy to understand.

[@Iyersukz6]

---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

[@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...

[@mrjana520]

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;

[@namangarg7023]

I was only strong in Power Bi before only but after watching and solving ur problems I have got interest in SQL

[@mantisbrains]

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!

[@shubhamsoni174]

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

[@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

[@vijeayanvj4367]

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

[@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

[@sanjayhc8672]

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;

[@TechWithViresh]

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;

[@namangarg7023]

u r superb like virendra sehwag...har ball pe chauka and 6😀

[@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)

[@skkholiya]

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

[@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

[@nirmalpatel3430]

cool question!! channel is treasure of learning!!

[@ankitamahajan3899]

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'

[@hariikrishnan]

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

[@SamienRaahhat]

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

[@SamienRaahhat]

@@abhiksaha3451 thanks for the correction.. edited it

[@chinmayjoshi7038]

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

[@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''

[@aswinc4829]

Grand explanation

[@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;

[@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)

[@mathavansg9227]

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)

[@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

[@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'

[@mrpam4672]

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

[@SumanSadhukhan-md4dq]

Awesome ❤

[@aaradhanasrivastava2757]

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

[@sahilsood2028]

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

[@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)

[@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)

[@VAR21723]

Good 👍👍👍👍❤❤

[@arvindkr974]

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

[@datadecoder_sai]

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;

[@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;

[@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 ;

[@AmanRaj-p8w]

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

[@ankitsaxena565]

Thanks sir 🙏

[@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.

[@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

[@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;

[@amritsinghania2722]

Sir it's for fresher interview question or experience one

[@ishashuklaa]

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;

[@rahulbehera112]

sir can you share what sql questions were asked in the online assessment of tredence analytics

[@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

[@bhumikalalchandani321]

same Leetcode question too

[@gameply347]

Sir please create a video on TIME function if possible.

[@ethyria7685]

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)

[@Apna_tahlka_123]

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

[@abhishekgarg9029]

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

[@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 ;

[@ZeeshanSyed-x5i]

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

[@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

[@sravyasrinivas]

Coudl you please add the DDL for the question

[@ankitbansal6]

Description box

[@ashanair1602]

I think the best method was the third.

[@girishsrinivasan2414]

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;

[@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

[@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)

[@ArunYadav-s2o]

method 2 is giving even two consecutive seats

[@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

[@MonkeyDLuffy4885]

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 )

[@RenuSingh-x8d]

Leetcode ka s h

[@syedzeeshan4742]

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

[@hrishiAOL]

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

[@ankitbansal6]

Change the video quality

[@edumail1016]

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)

[@meghnasoni]

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

[@Prakash-xn9hv]

@ankitbansal6 in coding question is python language is compulsory?

[@ZeeshanSyed-x5i]

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()