Find Subscribers Who Did Not Resubscribe Within Threshold Of Old Subscription

I have a table Subscriptions in PostgreSQL 10.5: id user_id starts_at ends_at -------------------------------- 1 233 02/04/19 03/03/19 2 233 03/04/19 04/03/19

Solution 1:

Not exists should do what you want:

select t.*
from t
where ends_at >= '2019-03-01' and ends_at < '2019-04-01' and
      not exists (select 1
                  from t t2
                  where t2.user_id = t.user_id and
                        t2.starts_at >= t.ends_at and
                        t2.starts_at <= t.ends_at + interval '2 day'
                 );