Count of users having a well-received post in that month

1

Please login or register to vote for this query.

(click on this box to dismiss)

"well-received": not closed, not deleted and positive score. Counts only the estimated state of the post 30 days after creation.

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

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

Enter Parameters

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