Customers who have been cancelled and within five days didn't have REJECTED or APPROVED applica-CodePudding

I have a table:

client_id	Date	Resolution
1	2022-10-15	CANCELLED
2	2022-10-25	CANCELLED
3	2022-10-16	CANCELLED
3	2022-10-17	REJECTED
4	2022-10-08	CANCELLED
4	2022-10-20	APPROVED
5	2022-10-03	CANCELLED
5	2022-10-04	APPROVED

Desired results:

client_id
1
2
4

I need to get all customers IDs who have been CANCELLED and within five days didn't have REJECTED or APPROVED the application. How can I achieve that?

CodePudding user response：

SELECT * FROM table t1
WHERE t1.Resolution = 'CANCELLED'
AND NOT EXISTS
(
   SELECT * FROM table t2
   WHERE t1.client_id = t2.client_id
   AND t2.Resolution IN ('REJECTED', 'APPROVED')
   t2.date < t1.date   interval '5 days'
)

CodePudding user response：

The solution:

select t1.client_id
from yourtable t1
left join yourtable t2
on t1.client_id = t2.client_id and
   t1.Resolution = 'CANCELLED' and
   t2.Resolution in ('REJECTED', 'APPROVED') and
   t2.date < t1.date   interval '5 days'
where t2.client_id is null

Explanation: We search for all t1 records that were cancelled and do not have a match in t2 that was either rejected or approved, so, the join condition searches for items with such pairs, defaulting to t2.* being null if there is no such pair for t1 and then applying a where filter that leaves only the t1 records having no such pairs in the result.