clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601440 fiddles created (47994 in the last week).

select version();
version
PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


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) );
 hidden batch(es)


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;
13 rows affected
email send_date open_date
Address_1 2021-05-02
Address_1 2021-05-02
Address_1 2021-05-03
Address_1 2021-05-05 2021-05-05
Address_1 2021-05-10
Address_1 2021-05-11 2021-05-11
Address_2 2021-05-01
Address_2 2021-05-01 2021-05-01
Address_2 2021-05-02
Address_2 2021-05-03
Address_2 2021-05-04 2021-05-04
Address_2 2021-05-05 2021-05-05
Address_2 2021-05-06
 hidden batch(es)


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
email send_date open_date groupid
Address_1 2021-05-02 0
Address_1 2021-05-02 0
Address_1 2021-05-03 0
Address_1 2021-05-05 2021-05-05 0
Address_1 2021-05-10 1
Address_1 2021-05-11 2021-05-11 1
Address_2 2021-05-01 1
Address_2 2021-05-01 2021-05-01 0
Address_2 2021-05-02 1
Address_2 2021-05-03 1
Address_2 2021-05-04 2021-05-04 1
Address_2 2021-05-05 2021-05-05 2
Address_2 2021-05-06 3
 hidden batch(es)


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;
email send_date open_date groupid
Address_1 2021-05-02 0
Address_1 2021-05-02 0
Address_1 2021-05-03 0
Address_1 2021-05-05 2021-05-05 0
Address_1 2021-05-10 1
Address_1 2021-05-11 2021-05-11 1
Address_2 2021-05-01 0
Address_2 2021-05-01 2021-05-01 0
Address_2 2021-05-02 1
Address_2 2021-05-03 1
Address_2 2021-05-04 2021-05-04 1
Address_2 2021-05-05 2021-05-05 2
Address_2 2021-05-06 3
 hidden batch(es)


-- -- 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;
email send_date open_date counttilopen
Address_1 2021-05-02
Address_1 2021-05-02
Address_1 2021-05-03
Address_1 2021-05-05 2021-05-05 4
Address_1 2021-05-10
Address_1 2021-05-11 2021-05-11 2
Address_2 2021-05-01 2021-05-01 1
Address_2 2021-05-01
Address_2 2021-05-02
Address_2 2021-05-03
Address_2 2021-05-04 2021-05-04 4
Address_2 2021-05-05 2021-05-05 1
Address_2 2021-05-06
 hidden batch(es)


-- -- @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
email send_date open_date counttilopen
Address_1 2021-05-02
Address_1 2021-05-02
Address_1 2021-05-03
Address_1 2021-05-05 2021-05-05 4
Address_1 2021-05-10
Address_1 2021-05-11 2021-05-11 2
Address_2 2021-05-01
Address_2 2021-05-01 2021-05-01 2
Address_2 2021-05-02
Address_2 2021-05-03
Address_2 2021-05-04 2021-05-04 3
Address_2 2021-05-05 2021-05-05 1
Address_2 2021-05-06
 hidden batch(es)