Interview Question 2 - Count of all Occurrence of status/color until it changes to another

 Reproduce table script

select * from (
select '1' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-03-01' reporting_month ,'GREEN' STATUS union all
select '1' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-01-01' reporting_month ,'GREEN' STATUS union all
select '3' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '4' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-06-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-02-01' reporting_month ,'GREEN' STATUS union all
select '6' id ,'2020-01-01' reporting_month ,'RED' STATUS  ) COMMULATIVE_SUM

Need in output , count of Status for first all occurrence of RED color (Occurrence will be end by GREEN color)

--Interview Question (SQL Query)
"Solution 1 "

select 
id
, min(NewRank)-1  
from
(
select 
id
, reporting_month
, STATUS
, case when STATUS = 'GREEN' then TRank else 99 end NewRank
, TRank
from
(
select 
DENSE_RANK() over (partition by id order by id, reporting_month desc) as TRank
, id
, reporting_month
, STATUS
from
COMMULATIVE_SUM
) d
) d2 
where 
NewRank <>99
group 
by id

"Solution 2 "
select 
id
,count(*)
from
(
select 
id,
sum(case when status = 'Red' then 0 else 1 end)
over(partition by id order by reporting_month desc) as r
from 
COMMULATIVE_SUM
)as t
where 
r=0
group by id