Voting scores by creation hour of week of posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH RawVotes AS (
  SELECT
    DATEDIFF(day, '1970-01-05 00:00:00',
      PostsWithDeleted.CreationDate) % 168 AS HourOfWeek,
    PostsWithDeleted.PostTypeId,
    Votes.VoteTypeId,
    COUNT(*) AS Count
  FROM PostsWithDeleted, Votes
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId IN (2, 3)
    AND
    PostsWithDeleted.PostTypeId IN (1, 2)
  GROUP BY
    DATEDIFF(day, '1970-01-05 00:00:00',
      PostsWithDeleted.CreationDate) % 168,
    PostsWithDeleted.PostTypeId,
    Votes.VoteTypeId
), RawPosts AS (
  SELECT
    DATEDIFF(day, '1970-01-05 00:00:00', CreationDate) % 168 AS HourOfWeek,
    PostTypeId,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE
    PostTypeId IN (1, 2)
  GROUP BY
    DATEDIFF(day, '1970-01-05 00:00:00', CreationDate) % 168,
    PostTypeId
)
SELECT
  RawVotes.HourOfWeek,
  VoteTypes.Name + ' on ' + PostTypes.Name AS VoteType,
  1.0 * RawVotes.Count / RawPosts.Count AS Count
FROM RawVotes, RawPosts, PostTypes, VoteTypes
WHERE
  RawVotes.PostTypeId = PostTypes.Id
  AND
  RawVotes.VoteTypeId = VoteTypes.Id
  AND
  RawVotes.PostTypeId = RawPosts.PostTypeId
  AND
  RawVotes.HourOfWeek = RawPosts.HourOfWeek
ORDER BY RawVotes.HourOfWeek, VoteType

Enter Parameters

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