select version();

CREATE TABLE test ( email TEXT NOT NULL, send_date DATE NOT NULL, open_date DATE NULL, CONSTRAINT sd_lt_od_ck CHECK ((open_date <= send_date) OR open_date IS NULL) );

INSERT INTO test VALUES ('Address_1', '2021-05-02', NULL), ('Address_1', '2021-05-02', NULL), ('Address_1', '2021-05-03', NULL), ('Address_1', '2021-05-05', '2021-05-05'), ('Address_1', '2021-05-10', NULL), ('Address_1', '2021-05-11', '2021-05-11'), ('Address_2', '2021-05-01', NULL), ('Address_2', '2021-05-01', '2021-05-01'), ('Address_2', '2021-05-02', NULL), ('Address_2', '2021-05-03', NULL), ('Address_2', '2021-05-04', '2021-05-04'), ('Address_2', '2021-05-05', '2021-05-05'), ('Address_2', '2021-05-06', NULL); SELECT * FROM test;

SELECT *, COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date) - CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END AS GroupId FROM test t

SELECT *, COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date, open_date NULLS FIRST) - CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END AS GroupId FROM test t ORDER BY email, send_date, open_date NULLS FIRST;

-- -- CharlyFace's solution -- SELECT Email, Send_Date, Open_Date, CASE WHEN Open_Date IS NOT NULL THEN COUNT(*) OVER (PARTITION BY Email, GroupId ORDER BY Send_Date) END AS CountTilOpen FROM ( SELECT *, COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date) - CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END AS GroupId FROM test t ) t;

-- -- @Vérace's solution -- SELECT Email, Send_Date, Open_Date, CASE WHEN Open_Date IS NOT NULL THEN COUNT(*) OVER (PARTITION BY Email, GroupId ORDER BY Send_Date) END AS CountTilOpen FROM ( SELECT *, COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date, open_date NULLS FIRST) -- note NULLS FIRST - CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END AS GroupId FROM test t ) t ORDER BY email, send_date, open_date NULLS FIRST; -- note NULLS FIRST