Mean up / down votes on questions and answers, historical timeline

1

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

WITH VoteRaw AS (
  SELECT DATEFROMPARTS(
    DATEPART(yyyy, Votes.CreationDate),
    DATEPART(mm, Votes.CreationDate),
    1) AS VoteMonth,
    PostsWithDeleted.PostTypeId,
    Votes.VoteTypeId
  FROM Votes, PostsWithDeleted
  WHERE Votes.PostId = PostsWithDeleted.Id
    AND PostsWithDeleted.PostTypeId IN (1, 2)
    AND Votes.VoteTypeId IN (2, 3)
),
VoteGroup AS (
SELECT VoteMonth, PostTypeId, VoteTypeId, COUNT(*) AS N
  FROM VoteRaw
  GROUP BY VoteMonth, PostTypeId, VoteTypeId
),
PostRaw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS PostMonth,
    PostTypeId
  FROM PostsWithDeleted
),
PostGroup AS (
  SELECT PostMonth, PostTypeId, COUNT(*) AS N
  FROM PostRaw
  GROUP BY PostMonth, PostTypeId
)
SELECT
  PostMonth AS Month,
  CASE
    WHEN VoteGroup.VoteTypeId = 2 THEN 'Upvote on '
    ELSE 'Downvote on '
  END + CASE
    WHEN VoteGroup.PostTypeId = 1 THEN 'Questions'
    ELSE 'Answers'
  END AS Type,
  CASE
    WHEN VoteGroup.VoteTypeId = 2 THEN 1.0
    ELSE -1.0
  END * VoteGroup.N / PostGroup.N AS Ratio
FROM PostGroup, VoteGroup
WHERE
  PostGroup.PostMonth = VoteGroup.VoteMonth
  AND PostGroup.PostTypeId = VoteGroup.PostTypeId
  AND PostGroup.PostMonth >= ##FromMonth:string?2000-01##+'-01'
ORDER BY Month DESC, VoteGroup.VoteTypeId, PostGroup.PostTypeId

Enter Parameters

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