Display consecutive days for which conditions are satisfied | Tricky SQL Interview Question

In this video we discuss a query which deal with finding consecutive days for which some conditions are met.

This is a very commonly asked interview questions and the concept used in the video can be used to solve similar queries.

DDL for table and DML for the tables used are given below:

--table stadium
--table creating
create table stadium(
id int,
visit_date date,
people int
)

--insert data into table stadium
insert into stadium(id,visit_date,people)
values(1,'2022-01-01',10),
(2,'2022-01-02',109),
(3,'2022-01-03',150),
(4,'2022-01-04',99),
(5,'2022-01-05',150),
(6,'2022-01-06',145),
(7,'2022-01-07',199),
(8,'2022-01-08',188),
(9,'2022-01-09',99),
(10,'2022-01-10',109),
(11,'2022-01-11',150),
(12,'2022-01-12',100),
(13,'2022-01-13',89),
(14,'2022-01-14',121),
(15,'2022-01-15',145)

#dataprojecthub
#data_project_hub
#sqlqueries #sqlinterviewquestions
#dataanalytics

[narsireddyavula]

to identify the consecutiveness better consider datecolumn rather id column. days_sub(date_column, new_derived_column)

[HARSHRAJ-gpve]

with cte as(
select stadium.*, ROW_NUMBER()OVER() as c1, DAY(visit_date) as d1 FROM stadium where people>100
), cte1 as(
select id, visit_date, people, (d1-c1) as e FROM cte
), cte2 as(
select e FROM cte1 GROUP BY e HAVING COUNT(id)>=3
)
select id, visit_date, people FROM cte1 JOIN cte2 ON cte1.e=cte2.e;

is it correct sir?

[kshamasathe]

What if there is no 'id' column in the table?