Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
select 1 as ID, 'N' as FLG from dual union all
 select 2 as ID, 'Y' as FLG from dual union all
 select 3 as ID, 'Y' as FLG from dual union all
 select 4 as ID, 'Y' as FLG from dual union all
 select 5 as ID, 'Y' as FLG from dual union all
 select 6 as ID, 'Y' as FLG from dual union all
 select 7 as ID, 'Y' as FLG from dual union all
 select 8 as ID, 'Y' as FLG from dual union all
 select 9 as ID, 'N' as FLG from dual union all
 select 10 as ID, 'Y' as FLG from dual union all
 select 11 as ID, 'N' as FLG from dual union all
 select 12 as ID, 'Y' as FLG from dual union all
 select 13 as ID, 'N' as FLG from dual union all
 select 14 as ID, 'N' as FLG from dual union all
 select 15 as ID, 'N' as FLG from dual union all
 select 16 as ID, 'Y' as FLG from dual union all
 select 17 as ID, 'N' as FLG from dual union all
 select 18 as ID, 'N' as FLG from dual union all
 select 19 as ID, 'N' as FLG from dual union all
 select 20 as ID, 'N' as FLG from dual union all
 select 21 as ID, 'N' as FLG from dual union all
 select 22 as ID, 'Y' as FLG from dual union all
 select 23 as ID, 'Y' as FLG from dual
23 rows affected

select *
from t
order by id


ID
FLG


1
N

2
Y

3
Y

4
Y

5
Y

6
Y

7
Y

8
Y

9
N

10
Y

11
N

12
Y

13
N

14
N

15
N

16
Y

17
N

18
N

19
N

20
N

21
N

22
Y

23
Y


select id, flg,
       (case when flg = 'Y' and
                  mod(row_number() over (partition by flg, seqnum - seqnum_2 order by id), 3) = 1
             then 'Y'
             else 'N'
        end) as flg1
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by flg order by id) as seqnum_2
      from t
     ) t
order by id


ID
FLG
FLG1


1
N
N

2
Y
Y

3
Y
N

4
Y
N

5
Y
Y

6
Y
N

7
Y
N

8
Y
Y

9
N
N

10
Y
Y

11
N
N

12
Y
Y

13
N
N

14
N
N

15
N
N

16
Y
Y

17
N
N

18
N
N

19
N
N

20
N
N

21
N
N

22
Y
Y

23
Y
N


with tt as (
       select t.*, row_number() over (order by id) as seqnum
       from t
     ),
     cte (seqnum, id, flg, flg1, counter) as (
      select seqnum, id, flg, flg,
             (case when flg = 'Y' then 1 else 0 end)
      from tt
      where seqnum = 1
      union all
      select tt.seqnum, tt.id, tt.flg,
             (case when cte.counter in (1, 2) then 'N'
                   when tt.flg = 'Y' then 'Y'
                   else 'N'
              end),
             (case when cte.counter in (1, 2) then cte.counter + 1
                   when tt.flg = 'Y' then 1
                   else 0
              end)
      from cte join
            tt
            on tt.seqnum = cte.seqnum + 1
     )
select *
from cte


SEQNUM
ID
FLG
FLG1
COUNTER


1
1
N
N
0

2
2
Y
Y
1

3
3
Y
N
2

4
4
Y
N
3

5
5
Y
Y
1

6
6
Y
N
2

7
7
Y
N
3

8
8
Y
Y
1

9
9
N
N
2

10
10
Y
N
3

11
11
N
N
0

12
12
Y
Y
1

13
13
N
N
2

14
14
N
N
3

15
15
N
N
0

16
16
Y
Y
1

17
17
N
N
2

18
18
N
N
3

19
19
N
N
0

20
20
N
N
0

21
21
N
N
0

22
22
Y
Y
1

23
23
Y
N
2


select flgs,
       substr(regexp_replace(flgs, 'Y(..|.$|$)', 'YNN'), 1, length(flgs)) as flg1s
from (select listagg(flg, '') within group (order by id) flgs
      from t
     )


FLGS
FLG1S


NYYYYYYYNYNYNNNYNNNNNYY
NYNNYNNYNNNYNNNYNNNNNYN

ID	FLG
1	N
2	Y
3	Y
4	Y
5	Y
6	Y
7	Y
8	Y
9	N
10	Y
11	N
12	Y
13	N
14	N
15	N
16	Y
17	N
18	N
19	N
20	N
21	N
22	Y
23	Y

ID	FLG	FLG1
1	N	N
2	Y	Y
3	Y	N
4	Y	N
5	Y	Y
6	Y	N
7	Y	N
8	Y	Y
9	N	N
10	Y	Y
11	N	N
12	Y	Y
13	N	N
14	N	N
15	N	N
16	Y	Y
17	N	N
18	N	N
19	N	N
20	N	N
21	N	N
22	Y	Y
23	Y	N

SEQNUM	ID	FLG	FLG1	COUNTER
1	1	N	N	0
2	2	Y	Y	1
3	3	Y	N	2
4	4	Y	N	3
5	5	Y	Y	1
6	6	Y	N	2
7	7	Y	N	3
8	8	Y	Y	1
9	9	N	N	2
10	10	Y	N	3
11	11	N	N	0
12	12	Y	Y	1
13	13	N	N	2
14	14	N	N	3
15	15	N	N	0
16	16	Y	Y	1
17	17	N	N	2
18	18	N	N	3
19	19	N	N	0
20	20	N	N	0
21	21	N	N	0
22	22	Y	Y	1
23	23	Y	N	2

ID	FLG
1	N
2	Y
3	Y
4	Y
5	Y
6	Y
7	Y
8	Y
9	N
10	Y
11	N
12	Y
13	N
14	N
15	N
16	Y
17	N
18	N
19	N
20	N
21	N
22	Y
23	Y

ID	FLG	FLG1
1	N	N
2	Y	Y
3	Y	N
4	Y	N
5	Y	Y
6	Y	N
7	Y	N
8	Y	Y
9	N	N
10	Y	Y
11	N	N
12	Y	Y
13	N	N
14	N	N
15	N	N
16	Y	Y
17	N	N
18	N	N
19	N	N
20	N	N
21	N	N
22	Y	Y
23	Y	N

SEQNUM	ID	FLG	FLG1	COUNTER
1	1	N	N	0
2	2	Y	Y	1
3	3	Y	N	2
4	4	Y	N	3
5	5	Y	Y	1
6	6	Y	N	2
7	7	Y	N	3
8	8	Y	Y	1
9	9	N	N	2
10	10	Y	N	3
11	11	N	N	0
12	12	Y	Y	1
13	13	N	N	2
14	14	N	N	3
15	15	N	N	0
16	16	Y	Y	1
17	17	N	N	2
18	18	N	N	3
19	19	N	N	0
20	20	N	N	0
21	21	N	N	0
22	22	Y	Y	1
23	23	Y	N	2

ID	FLG
1	N
2	Y
3	Y
4	Y
5	Y
6	Y
7	Y
8	Y
9	N
10	Y
11	N
12	Y
13	N
14	N
15	N
16	Y
17	N
18	N
19	N
20	N
21	N
22	Y
23	Y

ID	FLG	FLG1
1	N	N
2	Y	Y
3	Y	N
4	Y	N
5	Y	Y
6	Y	N
7	Y	N
8	Y	Y
9	N	N
10	Y	Y
11	N	N
12	Y	Y
13	N	N
14	N	N
15	N	N
16	Y	Y
17	N	N
18	N	N
19	N	N
20	N	N
21	N	N
22	Y	Y
23	Y	N

SEQNUM	ID	FLG	FLG1	COUNTER
1	1	N	N	0
2	2	Y	Y	1
3	3	Y	N	2
4	4	Y	N	3
5	5	Y	Y	1
6	6	Y	N	2
7	7	Y	N	3
8	8	Y	Y	1
9	9	N	N	2
10	10	Y	N	3
11	11	N	N	0
12	12	Y	Y	1
13	13	N	N	2
14	14	N	N	3
15	15	N	N	0
16	16	Y	Y	1
17	17	N	N	2
18	18	N	N	3
19	19	N	N	0
20	20	N	N	0
21	21	N	N	0
22	22	Y	Y	1
23	23	Y	N	2