Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');

CREATE TABLE



INSERT 0 6


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


cust_id
policy_id
end_date
flag
cust_id
flag
credit


1
a1
2022-10-12
inactive
1
active
good

1
a2
2022-10-20
inactive
1
active
good

1
a2
2023-05-15
active
1
active
good

2
a3
2022-12-12
inactive
2
inactive
bad

3
a3
2022-09-18
inactive
3
active
good

3
a1
2023-12-22
active
3
active
good



SELECT 6


SELECT t.cust_id, CASE WHEN t.flag = 'active' THEN 'good' ELSE 'bad' END as credit
  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


cust_id
credit


1
good

2
bad

3
good



SELECT 3

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