Oracle Analytical Function?

For the given table below, how can we find the customer whose name appears 3 times consecutively.

+---------+-----------+
| CUST_ID | CUST_NAME |
+---------+-----------+
| 1       | SAM       |
+---------+-----------+
| 2       | SAM       |
+---------+-----------+
| 3       | SAM       |
+---------+-----------+
| 4       | PETER     |
+---------+-----------+
| 5       | PETER     |
+---------+-----------+

Desired_Output

+-----------+
| CUST_NAME |
+-----------+
| SAM       |
+-----------+

Table Definition:

create table Customer
(
  cust_id int,
  cust_name varchar2(20)
);

insert into customer values (1, 'SAM');
insert into customer values (2, 'SAM');
insert into customer values (3, 'SAM');
insert into customer values (4, 'PETER');
insert into customer values (5, 'PETER');

Code Tried so far

Select distinct cust_name from (
select
cust_id,
cust_name,
lag(cust_name,1,0) over (order by cust_id)  as prev_cust_name,
lead(cust_name,1,0) over (order by cust_id) as next_cust_name
 from customer) a
 where a.prev_cust_name=a.next_cust_name;

I believe we can do this by using lead/lag to get the previous and next row. Although my solution gives the desired output but i don’t think this is correct solution.

Answer

Your method is close. You need one more comparison:

select distinct cust_name 
from (select c.*
             lag(cust_name) over (order by cust_id)  as prev_cust_name,
             lead(cust_name) over (order by cust_id) as next_cust_name
      from customer c
     ) a c
where prev_cust_name = cust_name and cust_name = next_cust_name;

For a more general solution, you can compare two lags:

select distinct cust_name 
from (select c.*
             lag(cust_id, 2) over (order by cust_id)  as prev2_cust_id,
             lag(cust_id, 2) over (partitioin by name order by cust_id)  as prev2_cust_id_name
      from customer c
     ) a c
where prev2_cust_id = prev2_cust_id_name;

This looks two rows back — once only by cust_id and once only for the name. If the cust_id values are the same, then all rows have the same name. You can adjust 2 to any value.