Q&A for scientists using computers to solve scientific problems
;with taggedPosts as ( Select pt.PostId from PostTags As pt Inner Join Tags As t ON t.Id = pt.TagId WHERE t.TagName = '##TagName##' ) , closevoters as ( select convert(int,json_value(voters.value , '$.Id')) as voterid , ph.comment from posthistory ph inner join taggedPosts tp on tp.postid = ph.postid cross apply openjson(ph.text, '$.Voters') voters where posthistorytypeid = 10 -- close event ), closed as ( select voterid as [User Link] , voterid , [1]+[2]+[3]+[4]+[7]+[10]+[20]+[101]+[102]+[103]+[104]+[105]+[199]+[201]+[202] total , [101] [Duplicate of...],[102] [Off-topic],[103] [Needs details or clarity],[104] [Needs more focus],[105] [Opinion-based],[1] [exact duplicate],[2] [off topic],[3] [not constructive],[4] [not a real question],[7] [too localized],[10] [general reference],[20] [noise or pointless], [199] [NotResolved],[201] [off-topic (channels)],[202] [Obsolete] from ( select voterid , comment from closevoters ) data pivot ( count(comment) FOR comment in ([101], [102], [103], [104], [105], [199], [201], [202], [1], [2], [3], [4], [7], [10], [20] ) ) pvt ), edits as( Select ph.UserId as [User Link], ph.UserId, Count(distinct ph.RevisionGUID) RevisionCount From PostHistory ph Join Posts p On ph.PostId = p.Id -- this is both Q and A Join Posts q On q.Id = IsNull(p.ParentId, p.Id) -- this is Qs + Qs for the As above Join PostTags pt On pt.PostId = q.Id Join Tags t On t.Id = pt.TagId Where ph.PostHistoryTypeId In (4, 5) -- Edit title, Edit body And t.TagName = '##TagName##' And ph.UserId > 0 --And p.DeletionDate Is Null -- Exclude deleted posts And p.OwnerUserId <> ph.UserId Group By ph.UserId Having Count(distinct ph.RevisionGUID) >= '##EditsThreshold##' --Order By RevisionCount Desc ) , all_users as ( select distinct userid from PostHistory ph Join Posts p On ph.PostId = p.Id -- this is both Q and A Join Posts q On q.Id = IsNull(p.ParentId, p.Id) -- this is Qs + Qs for the As above Join PostTags pt On pt.PostId = q.Id Join Tags t On t.Id = pt.TagId Where ph.PostHistoryTypeId In (4, 5, 10) -- Edit title, Edit body, Close And t.TagName = '##TagName##' ) Select a.userid , sum(e.revisioncount) revisioncount -- , sum(c.closecount) closecount from all_users a left outer join edits e on e.userid = a.userid left outer join closed c on c.voterid = a.userid group by a.userid