Get count of users who have not been billed since a certain month-CodePudding

I'm pretty sure the title is horribly written, but I have NO clue how to word it, nor my question. We are having an issue where it seems some users haven't been billed for a few months. Not all, just some, and we are trying to figure out how many. For example, there is a current, active user who has not been billed for about 3 months. There is another who hasn't been billed for about 5 months. There's also a few where they just didn't get billed last month, and etc...

I've been looking around, and because I'm pretty sure I'm not working my Google search correctly, I'm not finding a solution. After trying so many, the query I keep coming back to is:

SELECT COUNT(DISTINCT u.USER_ID) from SUBSCRIPTION AS s
JOIN USERS as U
ON s.USER_ID = u.USER_ID
WHERE s.BILLING_DATE <= DATEADD(month, -1, GETDATE()) AND u.STATUS = 'ACTIVE'

The above query, I believe is getting a much larger number than we should be getting back.

Please, any help is greatly appreciated, and thanks to all in advance.

CodePudding user response：

Here's something to try.

Description of subquery:

Find the users which have been billed within the last month or so.

Description of the outer query expression:

Find the count of active users not in the above list of users which have been billed.

SELECT COUNT(*)
  FROM users
 WHERE STATUS = 'ACTIVE'
   AND user_id NOT IN (
          SELECT user_id FROM subscription
           WHERE BILLING_DATE > DATEADD(month, -1, GETDATE())
       )
;

Adjust the dateadd logic as needed.

CodePudding user response：

Perhaps you need to look at the most recent subscription?

with data as (
    select *,
        row_number() over (partition by s.USER_ID order by s.BILLING_DATE desc) as rn
    from SUBSCRIPTION AS s inner join as s on u.USER_ID = s.USER_ID
    where u.STATUS = 'ACTIVE'
)
select *
from data
where rn = 1 and BILLING_DATE <= DATEADD(month, -1, GETDATE());