CODEWITHSUNDEEP
sqlpostgresql
Nickname_used
Nickname_used

Reputation: 428

Customers who have been cancelled and within five days didn't have REJECTED or APPROVED application

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?

Upvotes: 0

Views: 46

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76892

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.

Upvotes: 0

Related Questions