CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Bagzli
Bagzli

Reputation: 6569

Calculate Recurring User For 12 Months with SQL

I'm trying to see if there is a better way to achieve what I am doing right now. For example, I need to know total number of users who have logged in for the past 12 months. So each user who has logged in at least once a month, for twelve months in a row would count towards the total.

The way I am doing this right now is: I query my table and get all user ids and timestamps of when they were active and return them to my c# code. Then with bunch of loops and LINQ I calculate the value (Its too much code to dump into this question and since I'm trying to get away from doing it in c# I don't believe there is a need for it).

Now this takes some time to run and I'm sure there has to be a better way to do this with SQL. I've searched but haven't found any SQL functions that let you count based on a recurring condition.

For an answer I'm hoping to either get an example or a link to a similar SO question or an article that talks about achieving this.

An example of MyUsersTable:

UserId | Timestamp
1      | '2018-12-23 00:00:00.000'
1      | '2018-11-23 00:00:00.000'
1      | '2018-10-23 00:00:00.000'

EDIT: I did thought of using SUM(CASE WHEN month = 1 and month = 2 and month = 3) but that seems also like not a great solution.

Expected Result:

Total number of users who were active at least once a month in the last 12 months.

Upvotes: 2

Views: 1377

Answers (3)

Shawn
Shawn

Reputation: 4786

To get userIDs who logged in for a specific number of consecutive months, you can use:

/* These are your input values */
DECLARE @searchDate date = '2018-12-15' ;
DECLARE @monthsToSearch int = 12 ;

 /* First day of search month */    
DECLARE @EndDate   date = DATEADD(month, DATEDIFF(month, 0, @searchDate), 0) ;
 /* First day of month to search from */
DECLARE @StartDate date = DATEADD(month, -@monthsToSearch, @EndDate) ;

SELECT userID --, @StartDate AS startDate, @EndDate AS endDate
FROM (
    SELECT userID,  ( (YEAR(userLoginDT)*100)+MONTH(userLoginDT) ) AS datePoint /* YYYYMM */
    FROM t1
    WHERE userLoginDT >= @StartDate
        AND userLoginDT < @EndDate
) s1
GROUP BY userID
HAVING count(distinct(datePoint)) = @monthsToSearch
;

See the db<>fiddle here for my examples.

The fist two declared variables are your input variables. You feed it the date your are running the report on and then telling it how many months you want to go back to. So you can search any number of months. After that, it's pretty much date manipulation and math.

@EndDate essentially takes your declared date and calculates the first day of the month you are currently searching in. You will search for any dates before this date.

@StartDate counts back from your @EndDate to calculate the number of months you want to search.

(YEAR(userLoginDT)*100)+MONTH(userLoginDT) in your sub-select creates an integer variable that you can GROUP BY to get a distinct count of months you're searching over. This part could be sped up with the Calendar Table.

Then you just use the HAVING to pick out how many distinct records your want for @monthsToSearch.

NOTE: As many here can attest, I'm a huge fan of working with Calendar Tables when dealing with date calculations and large amounts of search data. Something like that would likely speed the query up a bit.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311188

I'd count the distinct number of months a user logged in on:

SELECT   userid
FROM     mytable
WHERE    YEAR(timestamp) = 2018
GROUP BY userid
HAVING   COUNT(DISTINCT MONTH(timestamp)) = 12

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269713

If you need users who logged in every month in 2018:

select ut.userid
from MyUsersTable ut
where timestamp >= '2018-01-01' and timestamp < '2019-01-01'
group by ut.userid
having count(distinct month(timestamp)) = 12;

Upvotes: 2

Related Questions