Voting scores by creation day of week of posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

WITH RawVotes AS (
  SELECT
    DATEDIFF(day, '1970-01-05', PostsWithDeleted.CreationDate) % 7 + 1
      AS DayOfWeek,
    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', PostsWithDeleted.CreationDate) % 7 + 1,
    PostsWithDeleted.PostTypeId,
    Votes.VoteTypeId
), RawPosts AS (
  SELECT
    DATEDIFF(day, '1970-01-05', CreationDate) % 7 + 1 AS DayOfWeek,
    PostTypeId,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE
    PostTypeId IN (1, 2)
  GROUP BY
    DATEDIFF(day, '1970-01-05', CreationDate) % 7 + 1,
    PostTypeId
)
SELECT
  RawVotes.DayOfWeek,
  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.DayOfWeek = RawPosts.DayOfWeek
ORDER BY RawVotes.DayOfWeek, VoteType

Enter Parameters

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