Distribution of User Activity Per Hour

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Distribution of User Activity Per Hour
-- Per hour distributiON of posts, comments and edits of a user.

declare @UserId int = ##UserId##

SELECT h.hours AS [Hour],
       COUNT(postsCounter) AS [Posts],
       COUNT(commentsCounter) AS [Comments],
       COUNT(suggestedCounter) AS [Suggested Edits],
       COUNT(editsCounter) AS [Edits],
       COUNT(counter) AS [Total Activity]
FROM(
   SELECT datepart(hour, creationdate) AS active, 1 AS counter,
                                                  1 AS postsCounter,
                                                  NULL AS commentsCounter,
                                                  NULL AS editsCounter,
                                                  NULL AS suggestedCounter
                                                  FROM posts WHERE owneruserid = @UserId
   UNION ALL
   SELECT datepart(hour, creationdate) AS active, 1 AS counter,
                                                  NULL AS postsCounter,
                                                  1 AS commentsCounter,
                                                  NULL AS editsCounter,
                                                  NULL AS suggestedCounter
                                                  FROM comments WHERE userid = @UserId
   UNION ALL
   SELECT datepart(hour, creationdate) AS active, 1 AS counter,
                                                  NULL AS postsCounter,
                                                  NULL AS commentsCounter,
                                                  NULL AS editsCounter,
                                                  1 AS suggestedCounter
                                                  FROM suggestededits WHERE owneruserid = @UserId
   UNION ALL
   SELECT datepart(hour, creationdate) AS active, 1 AS counter,
                                                  NULL AS postsCounter,
                                                  NULL AS commentsCounter,
                                                  1 AS editsCounter,
                                                  NULL AS suggestedCounter
                                                  FROM posthistory WHERE userid = @UserId
) AS a
right join(
   SELECT DISTINCT datepart(hour, CreationDate) AS hours FROM posts
) AS h ON a.active = h.hours

GROUP BY h.hours
ORDER BY h.hours

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results