1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM Votes);

SET @now = DATEFROMPARTS(YEAR(@now), MONTH(@now), 1);

WITH Raw AS (
  SELECT Votes.CreationDate
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId > 0
    AND
    Votes.VoteTypeId = 2
    AND
    Votes.CreationDate < @now
    AND (
      Posts.CommunityOwnedDate IS NULL
      OR
      Votes.CreationDate < Posts.CommunityOwnedDate
    )
  GROUP BY Posts.OwnerUserId, Votes.CreationDate
  HAVING COUNT(*) >= 20
)
SELECT
  DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
  COUNT(*) AS Count
FROM Raw
GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
ORDER BY Month

fork query

edited jan 8

created jan 8 peterh - PLEASE DELETE THIS ACCOUNT