1

Please login or register to vote for this query.

(click on this box to dismiss)

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

---the cte is taken from here:
---https://data.stackexchange.com/mathoverflow/query/825824/close-voters-on-question
;with closevoters_cte AS (
select postid as pid
     , cv.userid as uid
from posthistory ph
cross apply OPENJSON(JSON_QUERY(ph.text, '$.Voters'))
       with (userid int '$.Id') cv
where posthistorytypeid = ##type?11##
)

SELECT TOP(##num?500##)
  RANK() OVER(ORDER BY Count(pid) DESC) AS Rank,
  uid AS [User Link],
  COUNT(pid) AS [Count]
FROM closevoters_cte
GROUP BY uid
ORDER BY COUNT(pid) DESC

----- If PostHistoryTypeId in (10,11,12,13,14,15,19,20,35) this column will contain a JSON encoded string with all users who have voted for the PostHistoryTypeId
---10 = Post Closed - post voted to be closed
---11 = Post Reopened - post voted to be reopened
---12 = Post Deleted - post voted to be removed
---13 = Post Undeleted - post voted to be restored
---14 = Post Locked - post locked by moderator
---15 = Post Unlocked - post unlocked by moderator
---19 = Question Protected - question was protected by a moderator.
---20 = Question Unprotected - question was unprotected by a moderator.
---35 = Post migrated away - replaces id 17

fork query

edited jul 17

created jul 17 Martin20528