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##' ), 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##' ) -- Final query Select c."User Link", e."User Link", a.userid , sum(e.revisioncount) revisioncount -- edits count , sum(c.total) closecount -- close count , sum(c."Duplicate of...") duplicates , sum(e.revisioncount + c.total) Grand_Total -- edits + closevotes , sum(e.revisioncount + c.total - c."Duplicate of...") Total_no_Dupes -- total no dupes from all_users a left outer join edits e on e.userid = a.userid left outer join closed c on c.voterid = a.userid Where c.total > '##CloseThreshold##' Or e.revisioncount > '##EditsThreshold##' group by a.userid, c."User Link", e."User Link" order by Total_no_Dupes desc -- helper queries to build the main query, just copy paste select string_agg(concat('[', id, '] [', name, ']'), ',') [projection] , string_agg(concat('[', id, ']'), '+') [total] , string_agg(concat('[', id, ']'), ',') [pivots] from closereasontypes