Distinct Users/Sessions Per Hour

I was validating some information I found in BbStats (more on this building block later).

I wanted a break down by month/day/hour of the users/sessions. BbStats does this but you can't go backwards in time several months.

So say you want to find the usage between Sept 2010 and Dec 2010.

use bb_bb60_stats;
SELECT datepart(year,timestamp) as YEAR, DATEPART(month,timestamp) as MONTH, DATEPART(day,timestamp) AS DAY, DATEPART(hour,timestamp) AS HOUR, COUNT(DISTINCT user_pk1) AS DISTINCT_USERS, COUNT(DISTINCT session_id) as DISTINCT_SESSIONS
FROM [bb_bb60_stats].[dbo].[activity_accumulator]
where 
  user_pk1 > 4 and
  timestamp >= '2010-09-01 00:00:00' and
  timestamp < '2011-01-01 00:00:00'
group by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp)
order by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp) 


This would be the distinct users/sessions per hour.

Why "user_pk1 > 4"?  Turns out the first few user records come OOTB with Blackboard so I exclude them from activity.

Now, you can go down to the minute by extending this with DATEPART(minute, timestamp).  But that's a WHOLE lot of records.

Here's a variant to get down to the quarter hour (basically doing some math to convert minutes to the nearest 0, 15, 30, 45)

SELECT datepart(year,timestamp) as YEAR, DATEPART(month,timestamp) as MONTH, DATEPART(day,timestamp) AS DAY, DATEPART(hour,timestamp) AS HOUR, 15 * ROUND(DATEPART(minute,timestamp)/15,0) AS QHOUR, COUNT(DISTINCT user_pk1) AS DISTINCT_USERS, COUNT(DISTINCT session_id) as DISTINCT_SESSIONS
FROM [bb_bb60_stats].[dbo].[activity_accumulator]
where
  user_pk1 > 4 and
  timestamp >= '2010-09-01 00:00:00' and
  timestamp < '2011-01-01 00:00:00'
group by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp), 15 * ROUND(DATEPART(minute,timestamp)/15,0)
order by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp), 15 * ROUND(DATEPART(minute,timestamp)/15,0)


Results would look like:

YEAR MONTH DAY HOUR QHOUR DISTINCT_USERS DISTINCT_SESSIONS
2010 10 5 12 0 432 448
2010 10 5 12 15 407 427
2010 10 5 12 30 359 399
2010 10 5 12 45 380 406