SQL: Count Users with Activity in the Past Week

问题:

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

My data is as follow:

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

Which would result in the following table (only select dates shown)

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

The real data set has values for all dates in a continuous data range and the results table should have an entry for every date.

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

So far I have two queries that do something close-ish. The first returns the count of sessions in the past week by 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]

The other is from the following link which count the number of active users in a given date Active Users SQL query

I am in SQL Server 2012

I am having trouble combining the two.

Edit for clarification: the query I need likely won't have any getdate() or similar as I need to know how many users fit the 'active' criteria on Jan 1, today, and all the dates inbetween.

Thanks for any help!

回答1:

I think you just need to add a HAVING clause:

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

On your 10 in 30 query, just change your DATEADD() to have 30 days, and change the HAVING clause to be >= 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

I hope this helps.

回答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.

回答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

回答4:

The following query works:

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]