Find the users which have not responded in the last 3 days

Question

I am using the following PostgreSQL query

select unh.action_taken action_taken, un.user_id user_id 
from user_notification_history unh 
join user_notification un
on (unh.user_notification_id = un.id)
where unh.created_at between now() - interval '3 day' and now()
order by user_id asc

I get the following rows as a response

| action_taken | user_id |
| ------------ | ------- |
| done         | 1       |
| [null]       | 1       |
| [null]       | 1       |
| [null]       | 2       |
| [null]       | 2       |
| [null]       | 2       |
| [null]       | 3       |
| [null]       | 3       |
| [null]       | 3       |
| done         | 4       |
| [null]       | 4       |
| done         | 4       |

I want user_id, where the action_taken, is only [null] for the last 3 days, something like this

| action_taken | user_id |
| ------------ | ------- |
| [null]       | 2       |
| [null]       | 3       |

Answer 1

try like below using not exists with your query

select distinct unh.action_taken action_taken, un.user_id user_id 
from user_notification_history unh 
join user_notification un
on (unh.user_notification_id = un.id)
where unh.created_at between now() - interval '3 day' and now()
and not exists (select 1 from user_notification_history a
     where a.user_id=unh.user_id
      and a.action_taken='done')
order by user_id asc