1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for project managers

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

fork query

edited may 4 19

created may 4 19 peterh (2)