Successively down the rows, assign value to a column based on conditions in another columns

I want to populate the column New based on the values in column A and the comparison between columns B and C. I want the column New to have the initial value=1 from the start, and to have memory down the rows, but to reset to 1 at certain conditions.

New is set to 1 as initial value.

Let's look at row 3: Since A=40>30 and B=C, then New=New+1=2, since New=1 in row 2 above.

Let's look at row 6: Since A=40>30 and B<>C, then New=1 (counting starting over).

Creating initial table where New will be manipulated later on:

CREATE TABLE table_20220112
(
Ordered_by int,
A   float,
B   nvarchar(100) ,
C   nvarchar(100),
New int,
);
INSERT INTO table_20220112
VALUES
(1,10,'Apples','Apples',0),
(2,5,'Apples','Apples',0),
(3,40,'Apples','Apples',0),
(4,10,'Apples','Apples',0),
(5,50,'Apples','Apples',0),
(6,40,'Oranges','Apples',0),
(7,10,'Oranges','Apples',0),
(8,25,'Oranges','Bananas',0);
select * from table_20220112
--drop table table_20220112

Code logic would be something like this (I do not know the corresponding SQL-syntax):

New=1 (initail value before going in a looping down all rows)
If A<=30 Then
   IF B=C Then New=New
   Else if B<>C Then New=1
Else If A>30 Then 
   IF B=C Then New=New+1
   Else if B<>C Then New=1
END IF

Desired outcome:

Solution 1:

Use recursive cte and implement that logic using CASE expression

with rcte as
(
    select  Ordered_by, A, B, C, New = 1
    from    #table_20220112
    where   Ordered_by  = 1

    union all

    select  t.Ordered_by, t.A, t.B, t.C, 
            New = case when t.A <= 30 
                       then case when t.B = t.C then r.New else 1 end
                       when t.A >  30 
                       then case when t.B = t.C then r.New + 1 else 1 end
                       end
    from    rcte r
            inner join #table_20220112 t    on  r.Ordered_by    = t.Ordered_by - 1
)
select  *
from    rcte
order by Ordered_by