Count of users with a well-received question or answer by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Well-received means: 1) not closed (for questions) 2) not deleted 3) positive score after 30 days

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

WITH Raw AS (
  SELECT
    Posts.Id,
    MIN(Posts.OwnerUserId) AS UserId,
    MIN(DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(mm, Posts.CreationDate),
      1)) AS Month,
    MIN(Posts.PostTypeId) AS PostTypeId,
    SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE -1 END) AS Score
  FROM Posts, Votes
  WHERE
    Posts.PostTypeId BETWEEN 1 AND 2
    AND
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId > 0
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    Posts.ClosedDate IS NULL
    AND
    DATEDIFF(Day, Posts.CreationDate, Votes.CreationDate) BETWEEN 0 AND 30
  GROUP BY Posts.Id
)
SELECT
  Month,
  CASE
    WHEN PostTypeId = 1 THEN 'Question'
    ELSE 'Answer'
  END AS PostType,
  COUNT(DISTINCT UserId) AS Count
FROM Raw
WHERE Score > 0
GROUP BY Month, PostTypeId
ORDER BY Month DESC, PostType

Enter Parameters

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