SQL for Theater Seat Booking System

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