By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table users (id int, createdDate date, status varchar(50));
insert into users values(1, '2021-03-10', 'Active');
insert into users values(1, '2021-03-05', 'Pending');
insert into users values(1, '2021-03-07', 'Failed');
insert into users values(2, '2020-09-20', 'Pending');
insert into users values(2, '2020-09-01', 'Active');
insert into users values(2, '2020-07-01', 'Failed');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
with cte as
(
select id,createddate,status,row_number()over(partition by id order by createddate desc)rn from users
)
select id,createddate,status from cte a where rn=1 and status='Active'
and exists (select 1 from cte b where a.id=b.id and b.status='Pending' )
and exists (select 1 from cte b where a.id=b.id and b.status='Failed' )
| id | createddate | status |
|---|---|---|
| 1 | 2021-03-10 | Active |