查找未在旧订阅阈值内重新订阅的订阅者 [英] Find subscribers who did not resubscribe within threshold of old subscription

问题描述

我在PostgreSQL 10.5中有一个表Subscriptions：

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
3   296      02/09/19   03/08/19
4   126      02/01/19   02/28/19
5   126      03/01/19   03/31/19
6   922      02/22/19   03/22/19
7   111      01/22/19   02/21/19
8   111      02/22/19   03/21/19

我希望看到没有订阅的user_id在2天内开始他们三月份的订阅结束时。对于订阅在3月结束的用户。

I want to see user_ids who do not have a subscription that starts within 2 days of when their March subscription ended. For users with a subscription that ends in March.

鉴于上表，结果将是：

user_id
-------
296
126
922
111

我如何将3月份的查询放在一起？

How would I go about putting that query together for March?

推荐答案

不存在应该做什么：

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

这篇关于查找未在旧订阅阈值内重新订阅的订阅者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！