Question 90109 in PostgreSQL Question posted in
The official documentation can be found here.

create new column based on specific values another columns – Postgresql

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
Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search