Is there a way to find users with a certain set of badges?

1

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackoverflow.com/questions/362661

Stack Overflow

Q&A for professional and enthusiast programmers

with badgerank as 
-- per badge, how often does it occur
(select row_number() over (order by count(*) desc) as rank
     , name
     , count(*) as cnt
from badges
where tagbased = 0
group by name
)
-- calculate a ratio, higher is more rare
, badgerare as
(
    select cast(rank as decimal)/(select count(*) from badgerank) as ratio
    , name
    from  badgerank
)
-- per user, badge_name and its count
, userbadge as
(
  select userid
       , name
       , count(*) as [badge_cnt_user]
  from badges
  where tagbased = 0 
  group by userid, name
)
-- join users and badge to get ratio and badgecount
, userswithbadges as
(
   select b.name
        , b.ratio
        , u.userid
        , u.badge_cnt_user
   from badgerare b
   inner join userbadge u on b.name = u.name
)
-- union to get all data needed for the pivot table
, bdata as 
(
    select badge_cnt_user as id
         , name
         , userid
   from userswithbadges
   union 
   select count(*)
        , 'Count'
        , userid
   from userswithbadges
   group by userid
   union 
   select sum(ratio)
        , 'Ratio'
        , userid
   from userswithbadges
   group by userid
)

select top 1000 userid as [User Link],
     [Count],[Ratio],[Sheriff],[Illuminator],[Legendary],[Research Assistant],[Reversal],[Documentation Beta],[Not a Robot],[Epic],[Publicist],[Synonymizer],[Generalist],[Outspoken],[Refiner],[Archaeologist],[Convention],[Documentation Pioneer],[Marshal],[Sportsmanship],[Beta],[Copy Editor]
from (
select userid
     , name
     , id
from bdata
) as data
pivot
(
sum(id)
for name in ([Count],[Ratio],[Sheriff],[Illuminator],[Legendary],[Research Assistant],[Reversal],[Documentation Beta],[Not a Robot],[Epic],[Publicist],[Synonymizer],[Generalist],[Outspoken],[Refiner],[Archaeologist],[Convention],[Documentation Pioneer],[Marshal],[Sportsmanship],[Beta],[Copy Editor])
) as pvt
order by 3 desc


/*
to get the pivot columns run this:

declare @pivotcols nvarchar(max) =N'[Count],[Ratio]'

select @pivotcols = @pivotcols + ',[' +  name + ']'
from (
  select row_number() over (order by count(*) desc) as rank
       , name
  from badges
  where tagbased = 0
  group by name
) as data
order by rank desc
print @pivotcols
*/

Enter Parameters

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