Assign row number based on active flag and account id

1

Need to assign a row number based on active flag and account id

Below is the example data and expected output

Acctid | Acct_Date | Isactiveflag |Expected_output
123 | 2020-12-01 | 0 | 0
123 | 2021-01-01 | 0 | 0
123 | 2021-02-10 | 1 | 1
123 | 2021-03-01 | 0 | 0
123 | 2021-04-12 | 1 | 2
123 | 2021-05-16 | 1 | 3

Acctid | Acct_Date | Isactiveflag |Expected_output
321 | 2020-12-01 | 0 | 0
321 | 2021-01-01 | 0 | 0
321 | 2021-02-01 | 1 | 1
321 | 2021-03-01 | 0 | 0
321 | 2021-04-01 | 1 | 2

CREATE TABLE ACCT
(
Acctid int,
Acct_Date date,
IsAcitve int
);

INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2020-12-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2021-01-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2021-02-10' 1);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2021-03-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2021-04-12' 1);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (123,'2021-05-16' 1);

INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (321,'2020-12-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (321,'2021-01-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (321,'2021-02-01' 1);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (321,'2021-03-01' 0);
INSERT INTO ACCT (Acctid,Acct_Date, IsAcitve)
VALUES (321,'2021-04-01' 1);

2 
SELECT Acctid, Acct_Date, IsActive
	,CASE
		WHEN IsActive = 0
		THEN 0
		ELSE SUM(IsActive) OVER (PARTITION BY Acctid ORDER BY Acct_Date)
	END AS Expected_output
FROM ACCT;
3

Its working..