Query to find consecutive available seat in Theater or Bus.
Its most frequent asked question in interview and also required during data analysis.
create table thtr(rowid int, stno int, sts char(2))
insert into thtr values(1,1,'b'),(1,2,'b'),(1,3,'v'),(1,4,'b')
insert into thtr values(2,1,'b'),(2,2,'v'),(2,3,'v'),(2,4,'b')
insert into thtr values(3,1,'v'),(3,2,'v'),(3,3,'v'),(3,4,'v')
insert into thtr values(4,1,'v'),(4,2,'b'),(4,3,'v'),(4,4,'b')
insert into thtr values(5,1,'v'),(5,2,'b'),(5,3,'v'),(5,4,'v')
insert into thtr values(5,5,'v'),(5,6,'b'),(5,7,'v'),(5,8,'v')
declare @seatNeeded int =2
select rowid, count(*)/@seatNeeded as r_avl from (
select *,stno as s, stno-ROW_NUMBER() over (partition by rowid order by stno) as rn
from thtr where sts='V'
)t group by rowid,rn
having count(*)>=@seatNeeded
Q2) How many customers brought each product how many times during week?
;WITH CTE AS
(
SELECT PRD_ID, COUNT(DISTINCT CUSTID) AS NO_OF_CUST, COUNT(PRD_ID) AS NO_OF_TIMES , datepart(WEEK,pdate)AS PWEEK
FROM pur
GROUP BY prd_id,datepart(WEEK,pdate)
)
SELECT PRD_ID,SUM(NO_OF_CUST) AS NO_OF_CUST, NO_OF_TIMES FROM CTE
GROUP BY PRD_ID,NO_OF_TIMES,PWEEK
