Average post votes per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Votes casted in that month / Posts created in that month. Note, votes can be cast also for post created earlier.

Physics

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

WITH PostRaw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(m, CreationDate),
      1) AS Month,
    PostTypeId,
    COUNT(*) AS Count
  FROM PostsWithDeleted
  WHERE PostTypeId <= 2
  GROUP BY PostTypeId,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(m, CreationDate),
      1)
),
VoteRaw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(m, Votes.CreationDate),
      1) AS Month,
    PostsWithDeleted.PostTypeId,
    Votes.VoteTypeId,
    COUNT(*) AS Count
  FROM PostsWithDeleted, Votes
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    PostsWithDeleted.PostTypeId <= 2
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
  GROUP BY PostsWithDeleted.PostTypeId, Votes.VoteTypeId,
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(m, Votes.CreationDate),
      1)
)
SELECT
  PostRaw.Month,
  CASE WHEN PostRaw.PostTypeId = 1 THEN 'Question ' ELSE 'Answer ' END +
  CASE WHEN VoteRaw.VoteTypeId = 2 THEN 'Up' ELSE 'Down' END AS VoteType,
  (5.0 - 2.0 * VoteRaw.VoteTypeId) * VoteRaw.Count / PostRaw.Count AS MeanVote
FROM PostRaw, VoteRaw
WHERE
  PostRaw.Month = VoteRaw.Month
  AND
  PostRaw.PostTypeId = VoteRaw.PostTypeId
  AND
  PostRaw.Month > ##From:string?1970-01-01##
ORDER BY Month DESC, VoteType

Enter Parameters

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