My Activity By Local Hour

0

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

-- My Activity by Local Hour

WITH Hours AS (
       SELECT 1 AS hour
       UNION ALL
       SELECT hour + 1
       FROM Hours
       WHERE hour < 24),
     QuestionTime AS (
       SELECT DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate)) As Hour,
              COUNT(*) AS Count
       FROM Posts
       WHERE OwnerUserId = ##UserId## AND PostTypeId = 1
       GROUP BY DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate))),
     AnswerTime AS (
       SELECT DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate)) As Hour,
              COUNT(*) AS Count
       FROM Posts
       WHERE OwnerUserId = ##UserId## AND PostTypeId = 2
       GROUP BY DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate))),     
     CommentTime AS(
       SELECT DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate)) As Hour,
              COUNT(*) AS Count
       FROM Comments
       WHERE UserId = ##UserId##
       GROUP BY DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate))),       
     EditTime AS(
       SELECT DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate)) As Hour,
              COUNT(*) AS Count
       FROM PostHistory
       WHERE UserId = ##UserId## AND PostHistoryTypeId IN (4,5,6)
       GROUP BY DATEPART(HOUR, DATEADD(HH,##TimeZoneDiff##, CreationDate)))       

SELECT h.Hour,
       a.Count AS Answers,
       q.Count AS Questions,
       c.Count AS Comments,
       e.Count AS Revisions

FROM Hours h
LEFT OUTER JOIN AnswerTime a ON h.Hour = a.Hour
LEFT OUTER JOIN CommentTime c ON h.Hour = c.Hour
LEFT OUTER JOIN QuestionTime q ON h.Hour = q.Hour
LEFT OUTER JOIN EditTime e ON h.Hour = e.Hour
ORDER BY h.Hour

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results