CODEWITHSUNDEEP
sqlpostgresql
Raika
Raika

Reputation: 33

create new column based on specific values another columns

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

Upvotes: 3

Views: 66

Answers (3)

SelVazi
SelVazi

Reputation: 16063

we can use inner join as follows :

This one take on consideration if the last order is active or not.

Example if the last order of a customer is inactive he should be considered as bad customer :

SELECT *, CASE WHEN s.flag = 'active' THEN 'good' ELSE 'bad' END as credit
FROM exercise t
join (
  SELECT t.cust_id, t.flag
  FROM (
      select cust_id, max(end_date) as max_date
      from exercise
      group by cust_id
  ) r
  INNER JOIN exercise t
  ON t.cust_id = r.cust_id AND t.end_date = r.max_date
) as s on s.cust_id = t.cust_id

check it here : https://dbfiddle.uk/j0TtG62a

Upvotes: 1

gotqn
gotqn

Reputation: 43666

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

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;

Upvotes: 1

Related Questions