How to concatenate a conditional field and remove the same value

I am trying to create a column with a case statement, then concatenate the column. Here is an example code.

WITH base AS (
SELECT ID, Date, Action, case when (Date is null then Action || '**' else Action End) Action_with_no_date
FROM <Table_Name>
)
SELECT ID, "array_join"("array_agg"(DISTINCT Action_with_no_date), ', ') Action_with_no_date
FROM base
GROUP BY ID;

Basically, the Action_with_no_date will display the concatenation of values in Action with '**' string added to the values where Date is null for each ID

After I did this, I found an edge case.

If there is the same Action (i.e. play) taken for one ID, and if one action has date and the other one doesn’t, then the output will have one play and one play** for the ID

However, I want this to display just one play with **. Below is the example data for ID = 1

ID Date  Action
1  1/2/22 read
1  1/3/22 play
1  NULL   play

and expected result for the ID

ID Action_with_no_date
1  read, play**

How should I handle this?

Answer

You can calculate ** suffix if there is any row with null per id and action using analytic max() with case expression. Then concatenate suffix with action.

Demo:

with mytable as (
SELECT * FROM (
    VALUES
        (1, '1/2/22', 'read'),
        (1, '1/3/22', 'play'),
        (1, NULL, 'play')
) AS t (id, date, action)
)

select id, array_join(array_agg(DISTINCT action||suffix), ', ')
from
(
select id, date, action,
       max(case when date is null then '**' else '' end) over(partition by id, action) as suffix
  from mytable
)s
group by id

Result:

1   play**, read