T-SQL count number of events in a time window

Question

I have a Login Table like this:

Date,UserID
2020-08-01,1
2020-09-01,1
2020-09-07,2
2020-10-01,3
2020-10-12,4
2020-10-25,1

Basically, every time a user logins to my application, it register the date and the userID. I need to know the number of active users. To me an active user is one that has login in the previous thirty days of every day in the last week. For instance, let's say we are counting this week which goes from 2020-10-09 (October 9 2020) to 2020-10-15 (October 15 2020). Then I would go like this:

Check UserID = 1:

1. Has user login anytime between ('2020-10-09' - 30) and '2020-10-09'
2. Has user login anytime between ('2020-10-10' - 30) and '2020-10-10'

And so on until 2020-10-15

Then, it goes the same for each user ID.

In other words, I check the number of users that have login in a sliding Window of 30 days during a week. I hope this makes sense.

This is what I had in mind:

DROP TABLE IF EXISTS #ActiveUsers
CREATE TABLE #ActiveUsers (UserID bigint)

DECLARE @lowerDate Datetime='2020-10-09'
DECLARE @upperDate Datetime='2020-10-15'
DECLARE @activeSpan int=30

WHILE(@lowerDate <= @upperDate)
BEGIN
    SELECT e.UserID INTO #ActiveUsers FROM 
           (SELECT DISTINCT(UserID) FROM logins WHERE Date >= (@lowerDate - @activeSpan)) AS e 
           WHERE e.UserID NOT IN (SELECT UserID FROM #ActiveUsers)
    SET @lowerDate = @lowerDate + 1
END
PRINT SELECT COUNT(*) FROM #ActiveUsers

My reasoning was like this:

1. Check unique UserIDs in the timeframe.
2. Insert the unique ones in the temporary table as ling as they have not been previously inserted
3. Count the total after looping through the selected dates.

But besides being quiet inelegant I have not been able to make it work.

I would appraciate any advice.

Thank you!

Answer 1

I'm not sure if I understand the requirement - but let me confirm my understanding

Let's assume your 'upperDate' is 7 October. That means you would like to check that a user had done all of the following

• Had logged in between 1 Sep and 1 October
• Had logged in betweem 2 Sep and 2 October
• ...
• Had logged in between 7 Sep and 7 October

Now, imagine three users, all who had logged in only once

• The first logged in on 20 September
• The second logged in on 3 September
• The third logged in on 3 October

Their results would be as follows

• The first would count as active as they were identified by all 7 tests.
• The second would not count as active as they fail the tests from '4 Sep to 4 Oct' and later.
• The third would not count as active as they fail the tests from '1 Sep to 1 Oct' and '2 Sep to 2 Oct'.

In other words - I think all you need to do is find the logins between your upperdate minus 30 days, to upperdate minus 7 days.

As such you wouldn't need a loop - the easiest check is

DECLARE @upperDate Datetime = '20201015';
DECLARE @activeSpan int = 30;

SELECT DISTINCT(UserID) 
FROM   logins 
WHERE  [Date] >= DATEADD(day, - @activeSpan, @upperDate) 
       AND [Date] <= DATEADD(day, -7, @upperDate);