0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

;with closevoters_cte AS (
select ph.postid as pid
     , crt.name
     , cv.userid as uid
from posts p 
inner join PostTags pt on p.Id=pt.PostId
inner join Tags t on pt.TagId = t.Id
inner join posthistory ph on ph.PostId=p.Id
inner join closereasontypes crt on crt.id = comment
cross apply OPENJSON(JSON_QUERY(ph.text, '$.Voters'))
       with (userid int '$.Id') cv
where (posthistorytypeid = 10) and (comment in (1,101))
  and (t.TagName=##tagname:string?diophantine-equations##)
)

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

view current version

edited jun 30

created jun 30 anonymous