Mean rep gained on posts created on that month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, Posts.CreationDate),
    DATEPART(mm, Posts.CreationDate),
    1) AS Month,
  1.0 * SUM(
    CASE
      WHEN Votes.VoteTypeId = 1 THEN 17
      WHEN Posts.PostTypeId = 1 AND Votes.VoteTypeId = 2 THEN 5
      WHEN Posts.PostTypeId = 1 AND Votes.VoteTypeId = 3 THEN -2
      WHEN Posts.PostTypeId = 2 AND Votes.VoteTypeId = 2 THEN 10
      WHEN Posts.PostTypeId = 2 AND Votes.VoteTypeId = 3 THEN -3
      ELSE 0 -- unreachable
    END) / COUNT(*) AS Rep
FROM Posts, Votes
WHERE
  Votes.PostId = Posts.Id
  AND
  Votes.VoteTypeId BETWEEN 1 AND 3
  AND
  Posts.PostTypeId BETWEEN 1 AND 2
  AND
  DATEDIFF(day, Posts.CreationDate, Votes.CreationDate) BETWEEN 0 AND 30
GROUP BY DATEFROMPARTS(
  DATEPART(yyyy, Posts.CreationDate),
  DATEPART(mm, Posts.CreationDate),
  1)
ORDER BY Month DESC;

Enter Parameters

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