SQL: counting users with activity last week

Question

I am trying to count the number of users who have had at least two sessions for 7 days or ten days for 30 days of all dates.

My data is as follows:

Date UserID SessionID 1/1/2013 Bob1234 1 2/1/2013 Bob1234 2 2/2/2013 Bob1234 3 2/3/2013 Cal5678 4 

This will lead to the following table (show only selected dates)

 Date CountActiveUsers 1/1/2013 1 1/15/2013 0 2/2/2013 1 2/3/2013 2 

The actual data set has values ​​for all dates in a continuous range of data, and the result table should contain a record for each date.

SessionIDs are unique, and UserID always refers to the same person.

So far I have two queries that do something close. The first returns the number of sessions last week by the user:

 SELECT Count( d.[SessionID] ) As SessionPastWeek ,m.[UserID] ,m.[Date] FROM [Cosmos].[dbo].[Sessions_tbl] as m Inner Join [Cosmos].[dbo].[Sessions_tbl] as d on m.[UserID2] = d.[UserID] AND --Between does not work here for some reason d.[Date] <= m.[Date] AND d.[Date] > DATEADD(d,-7,m.[date]) Group By m.[UserID] ,m.[Date] 

Another from the following link, which counts the number of active users on a given date, SQL query of active users

I am in SQL Server 2012

I'm having trouble merging the two.

Edit for clarification: for the query that I need, there most likely will not be any getdate () or the like, since I need to know how many users meet the “active” criteria on January 1, today and all the dates between them.

Thanks for any help!

Answer 1

I think you just need to add a HAVING clause:

 HAVING COUNT(d.[SessionID]) >= 2 

In your query 10 out of 30, just change DATEADD () to 30 days and change the HAVING clause to> = 10.

  SELECT COUNT(d.[SessionID]) AS SessionPastPeriod , m.[UserID] , m.[Date] FROM Sessions_tbl AS m INNER JOIN Sessions_tbl as d ON m.UserID = d.UserID AND d.[Date] <= m.[Date] AND d.[Date] > DATEADD(d,-7,m.[Date]) GROUP BY m.UserID , m.[Date] HAVING COUNT(d.[SessionID]) >= 2 

Hope this helps.

Answer 2

You are too close.

 SELECT Count(d.[SessionID]) As SessionPastWeek ,m.[UserID] ,m.[Date] FROM [Cosmos].[dbo].[Sessions_tbl] as m Inner Join [Cosmos].[dbo].[Sessions_tbl] as d on m.[UserID2] = d.[UserID] --Between does not work here for some reason where --ADD where clause d.[Date] <= getdate() AND d.[Date] > DATEADD(d,-7,getdate()) Group By m.[UserID],m.[Date] having Count(d.[SessionID])>1 --The magical clause for you. 

Answer 3

 select count(*) from ( select UserID , sum(case when Date between dateadd(day, -7, getdate()) and getdate() then 1 end) as LastWeek , sum(case when Date between dateadd(day, -30, getdate()) and getdate() then 1 end) as Last30Days from Sessions_tbl group by UserID ) SubQueryAlias where LastWeek >= 2 or Last30Days >= 10 

Answer 4

The following query is executed:

 Select Count(UserID) As CountUsers ,[Date] From( SELECT COUNT(d.[SessionID]) AS SessionPastPeriod , m.[Date] , m.UserID FROM [Sessions_tbl] AS m INNER JOIN [Sessions_tbl] as d ON m.UserID = d.UserID AND d.[Date] <= m.[Date] AND d.[Date] > DATEADD(d,-7,m.[Date]) GROUP BY m.UserID ,m.[Date] HAVING COUNT(d.[SessionID]) >= 2) SQ Group By [Date]