If for one id_1 and for one id_2 rows exist with the following status but no row with status = 'delivered':

1. status = 'opened' AND status = 'clicked' then add new row with status = 'delivered'

is any way it can be done using sql or atleast find rows with that condition like find id_1 and id_2 that exist with status 'opened' and 'clicked' but not with 'delivered' in the table?

CodePudding user response：

Sounds like this should solve your problem. I create aggregated_statuses table that would check if conditions you mentioned are match in the base table, and if yes, creates new row that's appended to the base table in the final select

with base as (
  select 1 as id1, 'ca_1' as id2,'clicked' as status union all
  select 1 as id1, 'ca_1' as id2,'sent' as status union all
  select 3 as id1, 'ca_1' as id3,'clicked' as status union all
  select 3 as id1, 'ca_1' as id3,'sent' as status union all
  select 3 as id1, 'ca_1' as id3,'delivered' as status union all
  select 2 as id1, 'ca_2' as id2,'opened' as status 
),
aggregated_statuses as (
  select 
    id1,
    id2,
    'delivered' as status, 
    string_agg(status, '|') as agg_status 
  from base
  group by id1,id2
  having (agg_status like '%clicked%' and agg_status like '%sent%' and agg_status not like '