The reason why she didn't get answer while using tab1 is you cannot use alias names along with where or having because according to order of execution Select statement will be executed after where/having is executed. So it do not recognize the new name that is assigned in select statement (which is not executed btw) . The approach of using tab2 is brilliant. Or you can directly use occupancy+s1+s2+s3 in where statement when using tab1. Just wanted to share.
Different approach: with tab1 as( Select *, sum(occupancy) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int) rows between current row and 3 following) as sum_4_prec, lead(seat_number,3) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int)) as end_seat from cinema_tickets ) Select seat_number, end_seat from tab1 where end_seat is not null and sum_4_prec = 0
Great. Just in line 18 you could have mention where occupancy+s1+s2+s3=0 instead if creating another cte just use where clause. And thats the reason you got the error when u used the alias. Alias can't be used in where/having clauses. Good Job!!
@@rohansharma1046 Bro Because of Order of execution of SQL. WHERE clause runs before SELECT clause so there by occupany_sum column isn't create at that tym, Hope it helped.
with cte as( select seat_number,occupancy, sum(occupancy) over ( partition by substr(seat_number,1,1) rows between 3 preceding and current row)as rnk from cinema_tickets ) select seat_number from cte where rnk=0 and right(seat_number,1) not in(1,2,3); simple answer
with cte as (select *, lag(occupancy,1) over(partition by substring(seat_number, 1, 1)) as prev_occ, lag(occupancy, 2) over(partition by substring(seat_number, 1, 1)) as prev_occ2, lag(occupancy, 3) over(partition by substring(seat_number, 1, 1)) as prev_occ3 from cinema_tickets) select concat(start_seat, '-', seat_number) as vacant from (select concat(substring(seat_number, 1, 1), substring(seat_number, 2, 2) - 3) as start_seat, seat_number from cte where occupancy = 0 and prev_occ = 0 and prev_occ2 = 0 and prev_occ3 = 0) a
with cte as( SELECT *,cast(SUBSTRING(seat_number, 2, LEN(seat_number) - 1) as int) as sn, SUBSTRING(seat_number,1,1) as seat FROM cinema_tickets ),cte2 as( select *, lead(occupancy,1) over (partition by seat order by sn) as l1, lead(occupancy,2) over (partition by seat order by sn) as l2, lead(occupancy,3) over (partition by seat order by sn) as l3 from cte ) select seat_number,CONCAT(seat,sn+3) as succestive_next_seat from cte2 where occupancy+l1+l2+l3 = 0
Yes it works for all dbs sql server N other dbs. For oracle in cte just change in first cte. Like below.... With cte as( Select t. *, TO_NUMBER (SUBSTR(seat_number, 2,LENGTH(seat_number)-1)) as sn, SUBSTR(t.seat_number, 1,1) as seat From cinema_tickets t And same you can continue from cte2
hello, it would be possible for the query to be dynamic, for example, showing all the ranges of free seats per row. Example: A3 - A6 4 A9 - A10 2 B1 - B3 3 B8 - B10 3 C1 - C1 1 C3 - C3 1 C6-C6 1 C8-C9 2 Thank you
I have a bit different approach. i had directly used sum with over clause to complete it with one cte only: with cte as ( select *, sum(occupancy) over(partition by substring(seat_number,1,1) order by cast(substring(seat_number,2,LEN(seat_number)-1)as int) rows between current row and 3 following) total_occupancy_in_row, lead(seat_number,3) over(partition by substring(seat_number,1,1) order by cast(substring(seat_number,2,LEN(seat_number)-1)as int)) as last_seat from cinema_tickets ) select seat_number first_seat, last_seat from cte where total_occupancy_in_row = 0 and last_seat is not null
the last error was due to order of execution. You can't use alias name in where as "select" statement run after "where" clause. . . . . . . Btw your voice is ❤.
Also Select ans-4 ||"-"|| ans from( Select Case when ( Sum(occupancy) over (partition by substr(seat_no,1,1) order by substr(sear_no,2) asc rows between 4 preceding and current row) )=0 then seat_no else null end as ans From table)cte Where cte.ans is not null
My solution with cte1 as (select *, lead(occupancy) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next1, lead(seat_number) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat1 from cinema_tickets), cte2 as (select seat_number, lead(next1) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next2, lead(nextseat1) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat2 from cte1) , cte3 as(select seat_number,lead(next2) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next3, lead(nextseat2) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat3 from cte2), cte4 as (select seat_number, lead(next3) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next4, lead(nextseat3) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat4 from cte3), final1 as ( select cte1.*,cte2.next2,cte2.nextseat2,cte3.next3, cte3.nextseat3,cte4.next4,cte4.nextseat4 from cte1 inner join cte2 on cte1.seat_number=cte2.seat_number inner join cte3 on cte1.seat_number=cte3.seat_number inner join cte4 on cte1.seat_number=cte4.seat_number), final2 as ( select * from final1 where next1=next2 and next3=next4 and next2=next3), final as( select nextseat1,nextseat2,nextseat3,nextseat4 from final2) select nextseat1 as startseat,nextseat4 as lastseat,nextseat1 from final
@@Ananya-ji3uc currently iam learning data analyst course and iam completed SQL recently but my mentor doesn't tell me this things they only told us basic query and agreegate functions group by unions other some function only
Simple solution : Select * from (select case when A.occupancy+A.s1+A.s2+A.s3 = 0 then A.seat_number end as start_seat, case when A.occupancy+A.s1+A.s2+A.s3 = 0 then lead(A.seat_number,3) over() end as end_seat from (select seat_number,occupancy, lead(occupancy,1) over (partition by substr(seat_number,1,1)) s1, lead(occupancy,2) over(partition by substr(seat_number,1,1)) s2, lead(occupancy,3) over(partition by substr(seat_number,1,1)) s3 from cinema_ticket) A) B where B.start_seat is not null and B.end_seat is not null
SELECT concat(seat,' to ',concat(left(seat,1),right(seat,1)+3)) as avaibable_seats FROM ( SELECT seat, ns1, ns2, ns3, ns4 FROM ( SELECT seat, occupancy AS ns1, LEAD(occupancy, 1) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns2, LEAD(occupancy, 2) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns3, LEAD(occupancy, 3) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns4 FROM cinema_tickets ) AS s WHERE ns1 = 0 AND ns2 = 0 AND ns3 = 0 AND ns4 = 0 ) AS consecutive_empty_groups;
![ARGENTINA vs. BOLIVIA [6-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 10](http://i.ytimg.com/vi/C3wPtKNbAOA/mqdefault.jpg)
The reason why she didn't get answer while using tab1 is you cannot use alias names along with where or having because according to order of execution Select statement will be executed after where/having is executed.
So it do not recognize the new name that is assigned in select statement (which is not executed btw) .
The approach of using tab2 is brilliant. Or you can directly use occupancy+s1+s2+s3 in where statement when using tab1.
Just wanted to share.
Different approach:
with tab1 as(
Select *,
sum(occupancy)
over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int) rows between current row and 3 following)
as sum_4_prec,
lead(seat_number,3) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int)) as end_seat
from cinema_tickets
)
Select seat_number, end_seat
from tab1
where end_seat is not null
and sum_4_prec = 0
fck dude r u a human??
Great.
Just in line 18 you could have mention where occupancy+s1+s2+s3=0 instead if creating another cte just use where clause. And thats the reason you got the error when u used the alias. Alias can't be used in where/having clauses.
Good Job!!
Thanks, yes true that.
can you explain this further in detail?
@@rohansharma1046 Bro Because of Order of execution of SQL. WHERE clause runs before SELECT clause so there by occupany_sum column isn't create at that tym, Hope it helped.
Yeah, I tried doing that but it gives you a null value in end_seat. I am not sure why though it should be working tbh.
with cte as(
select seat_number,occupancy,
sum(occupancy) over (
partition by substr(seat_number,1,1) rows between 3 preceding and current row)as rnk
from cinema_tickets
)
select seat_number from cte where rnk=0 and right(seat_number,1) not in(1,2,3);
simple answer
with cte as
(select *, lag(occupancy,1) over(partition by substring(seat_number, 1, 1)) as prev_occ,
lag(occupancy, 2) over(partition by substring(seat_number, 1, 1)) as prev_occ2,
lag(occupancy, 3) over(partition by substring(seat_number, 1, 1)) as prev_occ3
from cinema_tickets)
select concat(start_seat, '-', seat_number) as vacant from
(select concat(substring(seat_number, 1, 1), substring(seat_number, 2, 2) - 3) as start_seat, seat_number from cte
where occupancy = 0 and prev_occ = 0 and prev_occ2 = 0 and prev_occ3 = 0) a
Great information and quality video. Glad I found it!
Looking good can you focus your good audio quality
with cte as(
SELECT *,cast(SUBSTRING(seat_number, 2, LEN(seat_number) - 1) as int) as sn,
SUBSTRING(seat_number,1,1) as seat
FROM cinema_tickets
),cte2 as(
select *,
lead(occupancy,1) over (partition by seat order by sn) as l1,
lead(occupancy,2) over (partition by seat order by sn) as l2,
lead(occupancy,3) over (partition by seat order by sn) as l3
from cte
)
select seat_number,CONCAT(seat,sn+3) as succestive_next_seat
from cte2
where occupancy+l1+l2+l3 = 0
A different approach
Yes it works for all dbs sql server N other dbs.
For oracle in cte just change in first cte. Like below....
With cte as(
Select t. *, TO_NUMBER (SUBSTR(seat_number, 2,LENGTH(seat_number)-1)) as sn,
SUBSTR(t.seat_number, 1,1) as seat
From cinema_tickets t
And same you can continue from cte2
can you help me understand how did you learn sql language and what is your background (As in how you are using sql in your job profile) @Ananya
Was this asked in initial hackerrank test?
can you send the query to create the given table
You can follow the github link in the description.
I think you forgot to add order by clause in all partitions which may skey you logic.
The dataset given to me was already sorted but yes if it weren't then order by clause would have helped.
When we use analytical fun, Order by is mandatory for sql server and oracle but not my sql.
hello, it would be possible for the query to be dynamic, for example, showing all the ranges of free seats per row. Example:
A3 - A6 4
A9 - A10 2
B1 - B3 3
B8 - B10 3
C1 - C1 1
C3 - C3 1
C6-C6 1
C8-C9 2
Thank you
Good Idea
I have a bit different approach. i had directly used sum with over clause to complete it with one cte only:
with cte as
(
select *,
sum(occupancy) over(partition by substring(seat_number,1,1) order by cast(substring(seat_number,2,LEN(seat_number)-1)as int) rows between current row and 3 following) total_occupancy_in_row,
lead(seat_number,3) over(partition by substring(seat_number,1,1) order by cast(substring(seat_number,2,LEN(seat_number)-1)as int)) as last_seat
from cinema_tickets
)
select seat_number first_seat, last_seat
from cte
where total_occupancy_in_row = 0 and last_seat is not null
the last error was due to order of execution. You can't use alias name in where as "select" statement run after "where" clause.
.
.
.
.
.
.
Btw your voice is ❤.
Also
Select ans-4 ||"-"|| ans from(
Select
Case when
(
Sum(occupancy) over (partition by substr(seat_no,1,1) order by substr(sear_no,2) asc rows between 4 preceding and current row)
)=0 then seat_no else null end as ans
From table)cte
Where cte.ans is not null
My solution
with cte1 as (select *,
lead(occupancy) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next1,
lead(seat_number) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat1
from cinema_tickets),
cte2 as (select seat_number,
lead(next1) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next2,
lead(nextseat1) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat2
from cte1)
,
cte3 as(select seat_number,lead(next2) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next3,
lead(nextseat2) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat3
from cte2),
cte4 as (select seat_number,
lead(next3) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as next4,
lead(nextseat3) over (partition by substring(seat_number,1,1) order by substring(seat_number,2,2)) as nextseat4
from cte3),
final1 as (
select cte1.*,cte2.next2,cte2.nextseat2,cte3.next3, cte3.nextseat3,cte4.next4,cte4.nextseat4
from cte1 inner join cte2 on cte1.seat_number=cte2.seat_number
inner join cte3 on cte1.seat_number=cte3.seat_number
inner join cte4 on cte1.seat_number=cte4.seat_number),
final2 as (
select * from final1
where next1=next2
and next3=next4
and next2=next3),
final as(
select nextseat1,nextseat2,nextseat3,nextseat4
from final2)
select nextseat1 as startseat,nextseat4 as lastseat,nextseat1
from final
Kya h ye
ke bawasir hai yo
Great explanation of the problem
The problem statement was not clear till i saw the final solution.
same with me
Thanks for the video, just wanted to say that your voice is too low 😅, Rest everything is fine, 🙏😊
Thanks for the suggestion, sure I'll work on that in the next videos.
It's for freshers or experienced
0-3 yrs of experience
@@Ananya-ji3uc currently iam learning data analyst course and iam completed SQL recently but my mentor doesn't tell me this things they only told us basic query and agreegate functions group by unions other some function only
@@vikramcena7131 okay, then you can practice from hackerrank/leetcode.
@@vikramcena7131 try datalemur
Instead of explaining give the problem statement first that would help us better , Thanks
Sure, will reflect on this suggestion in the upcoming videos.
good job!👍👍
Unclear problem statement
Simple solution :
Select * from
(select case when A.occupancy+A.s1+A.s2+A.s3 = 0 then A.seat_number end as start_seat,
case when A.occupancy+A.s1+A.s2+A.s3 = 0 then lead(A.seat_number,3) over() end as end_seat
from
(select seat_number,occupancy,
lead(occupancy,1) over (partition by substr(seat_number,1,1)) s1,
lead(occupancy,2) over(partition by substr(seat_number,1,1)) s2,
lead(occupancy,3) over(partition by substr(seat_number,1,1)) s3
from cinema_ticket) A) B
where B.start_seat is not null and B.end_seat is not null
SELECT concat(seat,' to ',concat(left(seat,1),right(seat,1)+3)) as avaibable_seats
FROM (
SELECT seat, ns1, ns2, ns3, ns4
FROM (
SELECT
seat,
occupancy AS ns1,
LEAD(occupancy, 1) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns2,
LEAD(occupancy, 2) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns3,
LEAD(occupancy, 3) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns4
FROM cinema_tickets
) AS s
WHERE ns1 = 0 AND ns2 = 0 AND ns3 = 0 AND ns4 = 0
) AS consecutive_empty_groups;