Leetcode Hard SQL Problem | Human Traffic of Stadium

In this video we are going to discuss a hard leet code problem called human traffic of stadium. We will be using SQL analytical functions to solve the problem. Do comment if you have a better solution.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table stadium (
id int,
visit_date date,
no_of_people int
);

insert into stadium
values (1,'2017-07-01',10)
,(2,'2017-07-02',109)
,(3,'2017-07-03',150)
,(4,'2017-07-04',99)
,(5,'2017-07-05',145)
,(6,'2017-07-06',1455)
,(7,'2017-07-07',199)
,(8,'2017-07-08',188);

#sql #dataengineer #leetcode

[puneetnaik]

Thanks for the logical solution . Achived the same using lead lag function

with CTE AS (
SELECT *,
lead(no_of_people, 1) over(order by visit_date) AS lead_1st_day,
lead(no_of_people, 2) over(order by visit_date) AS lead_2nd_day,
lag(no_of_people, 1) over(order by visit_date) AS lag_1st_day,
lag(no_of_people, 2) over(order by visit_date) AS lag_2nd_day
FROM STADIUM )
SELECT id, visit_date, no_of_people FROM cte WHERE no_of_people>=100 and ((lead_1st_day>=100 AND lead_2nd_day>=100) OR
(lag_1st_day>=100 and lag_2nd_day>=100))

[shekharagarwal]

Excellent Approach Ankit . Need to practise to adapt the thinking approach. I was used the legacy thinking approach :
with cte1 as
(Select *
, lag(no_of_people,1,0) over ( order by visit_date ) as prev_one_day_cnt
, lag(no_of_people,2,0) over ( order by visit_date ) as prev_two_day_cnt
, lead(no_of_people,1,0) over ( order by visit_date ) as next_one_day_cnt
, lead(no_of_people,2,0) over ( order by visit_date ) as next_two_day_cnt
from stadium)

Select visit_date, no_of_people
from cte1
where no_of_people >= 100 and
(
( prev_one_day_cnt >=100 and prev_two_day_cnt>=100 )
or
( prev_one_day_cnt >=100 and next_one_day_cnt>=100 )
or
(next_one_day_cnt>=100 and next_two_day_cnt>=100)
);

[mohammedriyaz]

once again thank you for your hard work ankit i tried this below one

with table1 (select a.*, lead(no_of_people, 1) over(order by visit_date ) as col1,

lead(no_of_people, 2) over(order by visit_date ) as col2,
lead(no_of_people, 3) over(order by visit_date ) as col3
from stadium a)
, table2(select b.*, case when no_of_people <= 100 or col1 <=100 or col2 <=100 then 0 else 1 end as col4 from table1 b)
select c.id, c.visit_date, c.no_of_people from table2 c where c.col4=1

[swapnilshimpi]

Good use case !!
Here is my approach.

select * from (
Select id, visit_date, no_of_people,
case when no_of_people>100
and (
min(no_of_people) over(order by id rows between current row and 2 following)>100
or
min(no_of_people) over(order by id rows between 1 preceding and 1 following)>100
or
min(no_of_people) over(order by id rows between 2 preceding and current row)>100
)

then 1 end rn
from stadium ) where rn=1
order by id;

[rahulkushwaha]

Great solution bhaiya.!

I came up with this, used lead() and lag() .

with
cte as(
select id, visit_date, no_of_people,
lag(no_of_people) over() as previous_day,
lead(no_of_people) over () as next_day
from stadium
),
cte2 as
(select id from cte
where no_of_people>100 and previous_day>100 and next_day>100)

select
id, visit_date, no_of_people from stadium
where id in(
select id from cte2
union
select id-1 from cte2
union
select id+1 from cte2
);



And as always learning a lot from your videos..
Thank you again for the efforts you put in.

[GaneshNaduvathati]

;with cte as(
select *, dateadd(dd, -ROW_NUMBER()over(order by visit_date), visit_date) as rwdate from stadium where no_of_people>=100
)
, ctegrp as
(select *, count(rwdate)over(partition by rwdate) as cnt from cte)
select id, visit_date, no_of_people from ctegrp where cnt>=3


Thanks a lot Ankit for the Great videos. Keep it up the good work.

[manaskukreja]

Hi Ankit, Thanks for making such wonderful videos. It is helping me a lot in understanding SQL much better. I have solved this problem by using your video where you have explain how to write advance SQL queries as follows:

with cte as(
select *,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as Prev_2,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as Prev_next_1,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2
from stadium)
select id, visit_date, no_of_people
from cte
where Prev_2 >=3
or Prev_next_1 >=3
or next_2 >= 3

[sahilummat]

Hello Ankit Sir Love your content
Here is an easy approach from my side .

with cte as (
select *,
row_number()over(order by id) as rn,
id-rn as ppl_grp
from stadium
where no_of_people >= 100
)
select id from (
select *,
count(1)over(partition by ppl_grp) as final_cnt
from cte)a
where a.final_cnt >=3

[KoushikT]

Thanks for the great content Ankit. I have used the date_add() to get the groups in my approach

with A as (
select *,
row_number() over (order by visit_date) as rno
from stadium
where no_of_people>100)

, B as
(select *, DATE_ADD(visit_date, INTERVAL -rno DAY) as comm
from A)

select id, visit_date, no_of_people from
(
select id,
visit_date, no_of_people,
count(comm) over (partition by comm) as cnt
from B
) C where cnt>=3

[dfkgjdflkg]

as usual great content. I am impressed. It is hard for me to comprehend how you lay out the problem. I guess it will take time for me to watch all your videos. Thanks a lot

[akash]

Hi Ankit, thanks for the problem :
My Solution :
WITH CTE as (
select *, Case when no_of_people>100 then 1 else 0 END as flag from stadium
)
, CTE1 as (
select *
, sum(flag) over(order by id asc ROWS between 2 preceding and current row) as preceeding
, sum(flag) over(order by id asc ROWS between current row and 2 following) as following
from CTE
)
select id, visit_date, no_of_people from CTE1 where preceeding>=3 or following>=3

[MixedUploader]

Ankit used the sum by prev_2, prev_next_1, next_2 learnt from your earlier video, same here below:
select * from
(select *,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as prev_2,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as prev_next_1,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2
from stadium) A
where prev_2=3 or prev_next_1=3 or next_2=3

[utkarshsingh]

Great video on Gaps & Islands problem, Thank you Ankit!

[manojyadav-ycdb]

This was my approach

with dep as (select dep_id, count(distinct emp_id) as emp_count
from emp
group by 1),
main as (select a.*,
case when emp_count = 1 then 3
when (sal_rank = 2 and emp_count = 2) then 3 else sal_rank end as final_rank
from (select *,
rank() over(partition by dep_id order by salary desc) as sal_rank
from emp
order by dep_id, salary desc) as a
left join dep as b on a.dep_id = b.dep_id)
select *
from main
where final_rank = 3

[mantisbrains]

select id, visit_date, no_of_people from
(select *, id - rn as diff from
(select *, row_number() over ()rn from
(select * from stadium where no_of_people > 100)a)b)d
where diff in
(select diff from
(select *, id - rn as diff from
(select *, row_number() over ()rn from
(select * from stadium where no_of_people > 100)a)b)g
group by diff
having count(diff) > 3)

Thanks, Ankit !

[litheshraju]

Thanks for the question Ankit. Below is my solution using lead, lag
with cte_use as
(select id, visit_date, no_of_people, case when no_of_people>=100 and isnull(lead(no_of_people, 1) over(order by visit_date), 0)>=100 and isnull(lead(no_of_people, 2) over(order by visit_date), 0)>=100 then 1 else 0 end as next_two,
case when no_of_people>=100 and isnull(lag(no_of_people, 1) over(order by visit_date), 0)>=100 and isnull(lag(no_of_people, 2) over(order by visit_date), 0)>=100 then 1 else 0 end as previous_two,
case when no_of_people>=100 and isnull(lag(no_of_people, 1) over(order by visit_date), 0)>=100 and isnull(lead(no_of_people, 1) over(order by visit_date), 0)>=100 then 1 else 0 end as current_previous_next
from stadium)
select * from cte_use
where next_two=1 or previous_two=1 or current_previous_next=1

[Tusharchitrakar]

Used a similar solution as yours based on the trick that I learnt from one of the earlier videos I saw. Im a beginner and I have learnt a lot in just 1 month thanks to your SQL videos. I have one broad question: I normally get the solution easily but end up writing too many ctes (please see the code attached below). Can you please give some pointers to reduce number of ctes? One way is the common use of the having clause (I have noticed it reduces the size by atleast 1 cte for obvious reasons) and sometimes a case can also be used for the same. But generally speaking can you make a video showing tips for efficient code writing that reduces number of ctes? That will really help thank you!

with cte1 as
(select *,
case when no_of_people>=100 then 1 else 0 end as eligible
from interview_test_32),
cte2 as
(select *, row_number() over(order by visit_date)
as rn from cte1 where eligible=1),
cte3 as
(select id, (id-rn) as group_no, visit_date, no_of_people from cte2),
cte4 as
(select *, count(1) over(partition by group_no) as count from cte3)
select id, visit_date, no_of_people from cte4 where count>=3;

[arpitagarwal]

Hello Ankit,

I have come with my own solution.

with cte as(
select *,
CASE
WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lead(no_of_people, 2) over(order by visit_date)>100 then 1
WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lag(no_of_people) over(order by visit_date)>100 then 1
WHEN no_of_people>100 and lag(no_of_people) over(order by visit_date)>100 and lag(no_of_people, 2) over(order by visit_date)>100 then 1
ELSE 0
END as "status"
from stadium)

select id, visit_date, no_of_people from cte where status=1;

Thanks for the video.

[Chathur]

with cte as
(select *, (id-row_no) as diff
from (
select *, row_number() over(order by visit_date) as row_no
from stadium
where no_of_people >=100) a),

cte_2 as
(select diff, count(diff)
from cte
group by diff
having count(diff)>3)

select id, visit_date, no_of_people
from cte
where diff in (select diff from cte_2)

[praveensinghrathore]

Thanks for the video, here is my solution. Learnt the concept from one of your previous videos.

WITH CTE AS (SELECT S.*,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as prev_day_count,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as curr_day_count,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as next_day_count
FROM Stadium S)
# SELECT * FROM CTE;
SELECT id, visit_date, people FROM CTE
WHERE curr_day_count >= 3 OR prev_day_count >= 3 OR next_day_count >= 3
ORDER BY visit_date ASC;