SQL SELF JOIN – driving me crazy

Can someone please tell me how to solve this question? And the thought process – like how do you think while solving this. It is driving me crazy. 🙁

Question – The attendance table logs the number of people counted in a crowd each day an event is held. Write a query to return a table showing the date and visitor count of high-attendance periods, defined as three consecutive entries (not necessarily consecutive dates) with more than 100 visitors.

Question code on oracle –

create table attendance (event_date date, visitors int);
insert into attendance values (to_date('01-01-20', 'mm-dd-yy'), 10);
insert into attendance values (to_date('01-04-20', 'mm-dd-yy'), 109);
insert into attendance values (to_date('01-05-20', 'mm-dd-yy'), 150);
insert into attendance values (to_date('01-06-20', 'mm-dd-yy'), 99);
insert into attendance values (to_date('01-07-20', 'mm-dd-yy'), 145);
insert into attendance values (to_date('01-08-20', 'mm-dd-yy'), 1455);
insert into attendance values (to_date('01-11-20', 'mm-dd-yy'), 199);
insert into attendance values (to_date('01-12-20', 'mm-dd-yy'), 188);

QUESTION TABLE AND DESIRED OUTPUT

Answer

There are several ways to approach this, but a self-join does not come to mind.

The most general would use a gaps-and-islands approach. However, I’m going to suggest a more brute-force method. Simply use lead() and lag() to get the values from the previous and next rows. Then use a where clause to see if there are three in a row that meet your condition:

select a.*
from (select a.*,
             lag(visitors, 2) over (order by event_date) as prev2_visitors,
             lag(visitors) over (order by event_date) as prev_visitors,
             lead(visitors, 2) over (order by event_date) as next2_visitors,
             lead(visitors) over (order by event_date) as next_visitors
      from attendance a
     ) a
where least(a.visitors, a.prev_visitors, a.prev2_visitors) > 100 or
      least(a.next_visitors, a.visitors, a.prev_visitors) > 100 or
      least(a.next2_visitors, a.next_visitors, a.visitors) > 100 ;

Here is a db<>fiddle.