create new column based on specific values another columns-CodePudding

Let say I have this kind of column

create table exercise (cust_id varchar, policy_id varchar, end_date date, flag varchar);   
insert into exercise (cust_id, policy_id, end_date, flag)
values
('1', 'a1', '2022-10-12', 'inactive'),
('1', 'a2', '2022-10-20', 'inactive'),
('1', 'a2', '2023-05-15', 'active'),
('2', 'a3', '2022-12-12', 'inactive')
('3', 'a3', '2022-09-18', 'inactive')
('3', 'a1', '2023-12-22', 'active')

from that column, we know that 3 customers (1, 2, 3) with 3 products(a1, a2, a3)
Is there any way to make new column,
if the customer have flag 'active' and 'inactive' then it called 'good',
but if the customer only have flag 'inactive' then it called 'bad'

so the output will be look like this, how the logic ???

cust_id	policy_id	end_date	flag	credit
1	a1	2022-10-12	inactive	good
1	a2	2022-10-20	inactive	good
1	a2	2023-05-15	active	good
2	a3	2022-12-12	inactive	bad
3	a3	2022-09-18	inactive	good
3	a1	2023-12-22	active	good

CodePudding user response：

We can use COUNT() here as a window function, along with conditional aggregation:

SELECT cust_id, policy_id, end_date, flag,
       CASE WHEN COUNT(CASE WHEN flag = 'active' THEN 1 END)
                     OVER (PARTITION BY cust_id) > 0
            THEN 'good' ELSE 'bad' END AS credit
FROM exercise
ORDER BY cust_id, end_date;

CodePudding user response：

Try this:

SELECT *
      ,CASE WHEN MAX(CASE WHEN flag = 'inactive' THEN 1 END) OVER (PARTITION BY cust_id) = 1 AND MAX(CASE WHEN flag = 'active' THEN 1 END) OVER (PARTITION BY cust_id) = 1 THEN 'good' ELSE 'bad' END
FROM exercise