Close stats for top dup-hammer users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Cognitive Sciences Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

select  hc.PostId as [Post Link], hr.CreationDate 'Reopened at'
, JSON_value(hr.text, concat('$.Voters[',
   (select min(value) -1 from
        (select value,
            JSON_VALUE(text, concat('$.Voters[',value,'].Id')) closevoteruserid
          from PostHistory as p
          cross join string_split('0,1,2,3,4,5,6,7',',')
          where p.PostHistoryTypeId = 10
          and p.PostId=hr.PostId
        ) data
        where closevoteruserid is null
      )
 ,'].DisplayName')) 'Reopened by',
 DATEDIFF(mi,hc.CreationDate,hr.CreationDate) 'time after reopen'

from PostHistory as hc

left join PostHistory hr on hr.PostId=hc.PostId 
and hr.CreationDate > hc.CreationDate and hr.PostHistoryTypeId=11

cross join string_split('0,1,2,3,4,5',',')

where hc.PostHistoryTypeId=10
and hc.Comment like '101%'
and hc.Text like '%BindingReason":{"GoldTagBadge%'
and JSON_value(hc.text, '$.Voters['+value+'].Id') is not null
and JSON_value(hc.text, '$.Voters['+value+'].Id') = '##userid:int##'
    

---and JSON_value(hc.text, concat('$.Voters[',
--
--     (select min(value) -1 from
--        (select value,
--            JSON_VALUE(text, concat('$.Voters[',value,'].Id')) closevoteruserid
--          from PostHistory as p
--          cross join string_split('0,1,2,3,4,5,6,7',',')
--          where p.PostHistoryTypeId = 10
--          and p.PostId=hc.PostId
--        ) data
--        where closevoteruserid is null
--      )
--
--   ,'].Id')) = '##userid:int##'

order by hc.CreationDate desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results