Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | 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 | > </pre> <!-- --> > 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) > ); > > <pre> > ✓ > </pre> <!-- --> > 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; > > <pre> 13 rows affected > > email | send_date | open_date > :-------- | :--------- | :--------- > Address_1 | 2021-05-02 | <em>null</em> > Address_1 | 2021-05-02 | <em>null</em> > Address_1 | 2021-05-03 | <em>null</em> > Address_1 | 2021-05-05 | 2021-05-05 > Address_1 | 2021-05-10 | <em>null</em> > Address_1 | 2021-05-11 | 2021-05-11 > Address_2 | 2021-05-01 | <em>null</em> > Address_2 | 2021-05-01 | 2021-05-01 > Address_2 | 2021-05-02 | <em>null</em> > Address_2 | 2021-05-03 | <em>null</em> > Address_2 | 2021-05-04 | 2021-05-04 > Address_2 | 2021-05-05 | 2021-05-05 > Address_2 | 2021-05-06 | <em>null</em> > </pre> <!-- --> > 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 > > <pre> > email | send_date | open_date | groupid > :-------- | :--------- | :--------- | ------: > Address_1 | 2021-05-02 | <em>null</em> | 0 > Address_1 | 2021-05-02 | <em>null</em> | 0 > Address_1 | 2021-05-03 | <em>null</em> | 0 > Address_1 | 2021-05-05 | 2021-05-05 | 0 > Address_1 | 2021-05-10 | <em>null</em> | 1 > Address_1 | 2021-05-11 | 2021-05-11 | 1 > Address_2 | 2021-05-01 | <em>null</em> | 1 > Address_2 | 2021-05-01 | 2021-05-01 | 0 > Address_2 | 2021-05-02 | <em>null</em> | 1 > Address_2 | 2021-05-03 | <em>null</em> | 1 > Address_2 | 2021-05-04 | 2021-05-04 | 1 > Address_2 | 2021-05-05 | 2021-05-05 | 2 > Address_2 | 2021-05-06 | <em>null</em> | 3 > </pre> <!-- --> > 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; > > <pre> > email | send_date | open_date | groupid > :-------- | :--------- | :--------- | ------: > Address_1 | 2021-05-02 | <em>null</em> | 0 > Address_1 | 2021-05-02 | <em>null</em> | 0 > Address_1 | 2021-05-03 | <em>null</em> | 0 > Address_1 | 2021-05-05 | 2021-05-05 | 0 > Address_1 | 2021-05-10 | <em>null</em> | 1 > Address_1 | 2021-05-11 | 2021-05-11 | 1 > Address_2 | 2021-05-01 | <em>null</em> | 0 > Address_2 | 2021-05-01 | 2021-05-01 | 0 > Address_2 | 2021-05-02 | <em>null</em> | 1 > Address_2 | 2021-05-03 | <em>null</em> | 1 > Address_2 | 2021-05-04 | 2021-05-04 | 1 > Address_2 | 2021-05-05 | 2021-05-05 | 2 > Address_2 | 2021-05-06 | <em>null</em> | 3 > </pre> <!-- --> > -- > -- 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; > > <pre> > email | send_date | open_date | counttilopen > :-------- | :--------- | :--------- | -----------: > Address_1 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-03 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-05 | 2021-05-05 | 4 > Address_1 | 2021-05-10 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-11 | 2021-05-11 | 2 > Address_2 | 2021-05-01 | 2021-05-01 | 1 > Address_2 | 2021-05-01 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-03 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-04 | 2021-05-04 | 4 > Address_2 | 2021-05-05 | 2021-05-05 | 1 > Address_2 | 2021-05-06 | <em>null</em> | <em>null</em> > </pre> <!-- --> > -- > -- @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 > > <pre> > email | send_date | open_date | counttilopen > :-------- | :--------- | :--------- | -----------: > Address_1 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-03 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-05 | 2021-05-05 | 4 > Address_1 | 2021-05-10 | <em>null</em> | <em>null</em> > Address_1 | 2021-05-11 | 2021-05-11 | 2 > Address_2 | 2021-05-01 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-01 | 2021-05-01 | 2 > Address_2 | 2021-05-02 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-03 | <em>null</em> | <em>null</em> > Address_2 | 2021-05-04 | 2021-05-04 | 3 > Address_2 | 2021-05-05 | 2021-05-05 | 1 > Address_2 | 2021-05-06 | <em>null</em> | <em>null</em> > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9b77b33c3d529e9919580eb95cc9ec41)*
back to fiddle