How do users rank in overall close votes (plus top 1000)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

;with closevoters(pid, uid, lvl)
as
(
select id
     , json_value(text, '$.Voters[0].Id')
     , 0
from posthistory ph
where posthistorytypeid = 10 
and json_value(text, '$.Voters[0].Id') is not null
union all
select id
     , json_value(text, concat('$.Voters[', lvl + 1, '].Id'))
     , lvl + 1
from posthistory ph
inner join closevoters cv on cv.pid = ph.id
where json_value(text, concat('$.Voters[', lvl + 1, '].Id')) is not null
)
, rankvoters as
(
select rank() over ( order by count(*) desc) [Ranking]
      , convert(int,uid) userid
     , count(*) [cnt]
from closevoters
where uid <> '-1' -- community user
group by uid
)

select 0 as 'cat'
     , ranking
     , userid as [User Link]
     , cnt [# closed questions]
from rankvoters
where userid in (select convert(int,value) from string_split(##compare:string?965146,578411##,','))
union 
select top 1000 
       1
     , ranking
     , userid as [User Link]
     , cnt
from rankvoters
order by cat, ranking

Enter Parameters

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