1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- 
;with closevoters_rank as
(
select row_number() over(order by count(*) desc) [row]
     , userid
     , count(*) as cnt
from 
(
select json_value(text, concat('$.Voters[', keys.value, '].Id')) userid
      , postid
from posthistory
cross apply string_split('0,1,2,3,4',',') keys
where posthistorytypeid = 10 -- close
) closevoters
where userid is not null
and userid > 0
group by userid
)

select ' '
     , row
     , 'site://users/' + userid + '|' + u.displayname [close voter]
     , cnt [#posts closed]
from closevoters_rank
inner join users u on u.id = userid
where userid = ##userid:string##
union
select '.'
     , row
     , 'site://users/' + userid + '|' + u.displayname
     , cnt
from closevoters_rank
inner join users u on u.id = userid
order by 1, 2

fork query

edited jul 12 18

created jul 12 18 rene