SQL Server 2019

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

create table dbo.cluster_test
(student_id int not null
,record_date date not null
,section varchar(30) null)

insert into cluster_test
(student_id, record_date, section)
values
 (123, '2020-02-06', NULL)
,(123, '2020-05-14', 'A')
,(123, '2020-08-12', 'A')
,(123, '2020-09-01', 'B')
,(123, '2020-09-15', 'A')
,(123, '2020-09-29', 'A')
,(123, '2020-11-02', NULL)
,(123, '2020-11-30', NULL)
,(789, '2020-01-12', NULL)
,(789, '2020-04-12', 'A')
,(789, '2020-05-03', NULL)
,(789, '2020-06-13', 'A')
,(789, '2020-06-30', 'B')
,(789, '2020-07-01', 'B')
,(789, '2020-07-22', 'A')
15 rows affected

Select student_id, record_date, section,
       Case when section is not null 
            Then sum(case when section is not null and (section <> lgs or lgs is null) then 1 end) 
                    over (partition by student_id order by record_date)
       End as flag
  From 
(select student_id, record_date, section,
       Lag(section) over (partition by student_id order by record_date) as lgs
  From cluster_test t) t
 order by student_id, record_date


student_id
record_date
section
flag


123
2020-02-06
null
null

123
2020-05-14
A
1

123
2020-08-12
A
1

123
2020-09-01
B
2

123
2020-09-15
A
3

123
2020-09-29
A
3

123
2020-11-02
null
null

123
2020-11-30
null
null

789
2020-01-12
null
null

789
2020-04-12
A
1

789
2020-05-03
null
null

789
2020-06-13
A
2

789
2020-06-30
B
3

789
2020-07-01
B
3

789
2020-07-22
A
4


Warning: Null value is eliminated by an aggregate or other SET operation.

student_id	record_date	section	flag
123	2020-02-06	null	null
123	2020-05-14	A	1
123	2020-08-12	A	1
123	2020-09-01	B	2
123	2020-09-15	A	3
123	2020-09-29	A	3
123	2020-11-02	null	null
123	2020-11-30	null	null
789	2020-01-12	null	null
789	2020-04-12	A	1
789	2020-05-03	null	null
789	2020-06-13	A	2
789	2020-06-30	B	3
789	2020-07-01	B	3
789	2020-07-22	A	4