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

Postgresql – 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?

Tags:

2

Answers


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

    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.

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

Back To Top
Search