CODEWITHSUNDEEP
sqlpostgresqldate-range
hummmingbear
hummmingbear

Reputation: 2394

Find users who did not re-subscribe

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

I'd like to get a list of user ids who did not re-subscribe in march. Given the data above, it should show:

user_id
-------
296
922

How would I go about calculating this. I've given a few queries a try, but they don't work and aren't worth posting

Upvotes: 0

Views: 66

Answers (3)

Zack
Zack

Reputation: 2341

In addition to the other answers, here a re a couple of other options:

Option 1

You can create 2 CTEs, one for each month (assuming you're looking at particular months, and not just February/March in general). Note that this uses the range data type to filter the dates.

WITH 
    -- sample data
    Subscriptions("id", user_id, starts_at, ends_at) AS
    (
        VALUES
        (1,   233,      DATE'02/04/19',   DATE'03/03/19'),
        (2,   233,      DATE'03/04/19',   DATE'04/03/19'),
        (3,   296,      DATE'02/09/19',   DATE'03/08/19'),
        (4,   126,      DATE'02/01/19',   DATE'02/28/19'),
        (5,   126,      DATE'03/01/19',   DATE'03/31/19'),
        (6,   922,      DATE'02/22/19',   DATE'03/22/19')
    ),
    -- separate CTEs for February and March data
    -- using range type for easy filter.
    FebruarySubscriptions AS
    (
        SELECT * FROM Subscriptions 
        WHERE daterange('2019-02-01', '2019-03-01') @> starts_at
    ),
    MarchSubscriptions AS
    (
        SELECT * FROM Subscriptions 
        WHERE daterange('2019-03-01', '2019-04-01') @> starts_at
    )
SELECT * 
FROM FebruarySubscriptions
    LEFT JOIN MarchSubscriptions ON
        MarchSubscriptions.user_id = FebruarySubscriptions.user_id
WHERE MarchSubscriptions."id" IS NULL

Option 2

Use LEAD window function to figure out which users don't have a re-subscription. The benefit of this option is it's more extensible.

WITH 
    Subscriptions("id", user_id, starts_at, ends_at) AS
    (
        VALUES
        (1,   233,      DATE'02/04/19',   DATE'03/03/19'),
        (2,   233,      DATE'03/04/19',   DATE'04/03/19'),
        (3,   296,      DATE'02/09/19',   DATE'03/08/19'),
        (4,   126,      DATE'02/01/19',   DATE'02/28/19'),
        (5,   126,      DATE'03/01/19',   DATE'03/31/19'),
        (6,   922,      DATE'02/22/19',   DATE'03/22/19')
    ),
    Resubscriptions(user_id, current_subscription, next_subscription) AS
    (
        SELECT 
            user_id, 
            starts_at, 
            LEAD(starts_at) OVER
            (
                PARTITION BY user_id
                ORDER BY starts_at ASC
            )
        FROM Subscriptions
    )
SELECT * 
FROM Resubscriptions
WHERE 
    daterange('2019-02-01', '2019-03-01') @> current_subscription
    AND next_subscription IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Presumably, you want a specific March, not March of any year. So:

select s.userId
from subscriptions s
group by s.userId
having count(*) filter (where startsAt >= '2019-03-01' and startsAt < '2019-04-01') = 0;

You can use not exists as well. This works better if you have a list of users:

select u.*
from users u
where not exists (select 1
                  from subscriptions s
                  where s.userid = u.userid and
                        s.startsAt >= '2019-03-01' and
                        s.startsAt < '2019-04-01'
                 );

Instead of users, you can also use:

select distinct s.userId
from subscriptions
where . . .

Upvotes: 1

Avi
Avi

Reputation: 1845

You can make use of not exists and don't get the customers who have start date of March. 

with cte as 
(
select 1  as ID,   233 as User_Id, '02/04/2019' as Startsat   , '03/03/2019' ends_at union all 
select 2  as ID,   233 as User_Id, '03/04/2019' as Startsat   , '04/03/2019' ends_at union all 
select 3  as ID,   296 as User_Id, '02/09/2019' as Startsat   , '03/08/2019' ends_at union all 
select 4  as ID,   126 as User_Id, '02/01/2019' as Startsat   , '02/28/2019' ends_at union all 
select 5  as ID,   126 as User_Id, '03/01/2019' as Startsat   , '03/31/2019' ends_at union all 
select 6  as ID,   922 as User_Id, '02/22/2019' as Startsat   , '03/22/2019' ends_at)

select *  from cte  c 
where  not exists
(select 1 from cte c1 where c.User_Id = c1.User_Id and date_part('Month',to_date(c1.Startsat,'MM/DD/YYYY'))= '3' )

Output: 

id  user_id startsat    ends_at
3   296 02/09/2019  03/08/2019
6   922 02/22/2019  03/22/2019

Here is the fiddle link:

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=84e24cd517fa0810bef011d6fb1b2be2

Upvotes: 0

Related Questions