CREATE TABLE table_ (
User_ID varchar(40),
Month date,
Active integer);
INSERT INTO table_ (User_ID, Month, Active) VALUES
('11M8CIG5','2021-12-01',1),
('11M8CIG5','2022-01-01',0),
('11M8CIG5','2022-02-01',1),
('11M8CIG5','2022-03-01',1),
('11M8CIG5','2022-04-01',1),
('11M8CIG5','2022-05-01',0),
('11M8CIG5','2022-06-01',1),
('11M8CIG5','2022-07-01',1),
('11M8CIG5','2022-08-01',0),
('11M8CIG5','2022-09-01',1);
select User_ID, month, active,
row_number() over(partition by User_ID, X order by month)-1 D
from (
select User_ID, month, active, count(case when active != 1 then 1 end) over(partition by User_ID order by month) X
from table_
) Q
Telegram: DB developers channel