Top 50 badge earners of each class

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

--- Returns the top 50 badge earners of each badge class (gold, silver, bronze)

With UserBadgeTypeCount (UserId, BronzeCount, SilverCount, GoldCount) as (
  Select UserId,
         Sum(Case When Class = 3 then 1 else 0 end) as BronzeCount,
         Sum(Case When Class = 2 then 1 else 0 end) as SilverCount,
         Sum(Case When Class = 1 then 1 else 0 end) as GoldCount
   From Badges
   Group by UserId
)

select * from 
(
  Select top 50 
     1 as [BadgeType], 
     'Gold' as [BadgeLabel], 
     UserId as [User Link], 
     GoldCount as [Count]
  From UserBadgeTypeCount
  Order by GoldCount Desc
) ggg
union
select * from 
(
  Select top 50 
     2 as [BadgeType], 
     'Silver' as [BadgeLabel], 
     UserId as [User Link], 
     SilverCount as [Count]
  From UserBadgeTypeCount
  Order by SilverCount Desc
) sss
union
select * from 
(
  Select top 50 
     3 as [BadgeType], 
     'Bronze' as [BadgeLabel], 
     UserId as [User Link], 
     BronzeCount as [Count]
  From UserBadgeTypeCount
  Order by BronzeCount Desc
) bbb
Order by BadgeType, Count Desc

Enter Parameters

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