1

Please login or register to vote for this query.

(click on this box to dismiss)

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 as(
Select c."User Link" [CloseVoted ID] , 
       e."User Link" [Editor ID],
       a.userid [User ID]
    , 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
)

Select * from Final_Query 
  Where Total_no_Dupes > '##TotalThreshold##'
  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

view current version

edited jun 2 23

created may 31 23 MdoubleDash