Count of users by month and the got upvotes in that month

1

Please login or register to vote for this query.

(click on this box to dismiss)

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

DECLARE @TsNow DATETIME;

SELECT @TsNow = DATEFROMPARTS(
  DATEPART(yyyy, MAX(CreationDate)),
  DATEPART(mm, MAX(CreationDate)),
  1)
FROM Posts;

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(mm, Votes.CreationDate),
      1) AS Month,
    Posts.OwnerUserId AS UserId,
    CASE
      WHEN COUNT(*) > ##MaxUpvotePerMonth:int?5##
      THEN ##MaxUpvotePerMonth##
      ELSE COUNT(*)
    END AS UpvoteCount
  FROM Posts, Votes
  WHERE
    Posts.PostTypeId = 2
    AND
    Posts.OwnerUserId > 0
    AND
    Posts.Id = Votes.PostId
    AND
    Votes.VoteTypeId = 2
    AND
    Votes.CreationDate < @TsNow
  GROUP BY
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(mm, Votes.CreationDate),
      1),
    Posts.OwnerUserId
)
SELECT
  Month,
  CASE
    WHEN UpvoteCount < ##MaxUpvotePerMonth##
    THEN CAST(UpvoteCount AS VARCHAR(MAX))
    ELSE CAST(UpvoteCount AS VARCHAR(MAX)) + ' or more'
  END AS UpvoteCount,
  COUNT(*) AS UserCount
FROM Raw
GROUP BY Month, UpvoteCount
ORDER BY Month DESC, UpvoteCount

Enter Parameters

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