Users and Badges

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Find and classify all badge awards for people
-- display summary of gold/silver/bronze awards, and also
-- for tag, or regular badges.
-- Results can be large, so limit it by users with a configurable
-- minimum number of badges.

with BadgeClass as (

  -- For all regular badges, link in the metal
  -- this has to be done manually as there is no reference
  -- in the database for it.
  
  -- tag badges all have lower-case names, other badges start with an upper-case.
  
  select distinct
         Name,
         case when LOWER(Name) = Name
                   then 'Tag'
              when Name in ('Socratic', 'Stellar Question', 'Great Question', 
                            'Famous Question', 'Illuminator', 'Great Answer', 
                            'Populist', 'Reversal', 'Unsung Hero', 'Fanatic', 
                            'Legendary', 'Marshal', 'Constable', 'Sheriff', 
                            'Steward', 'Copy Editor', 'Electorate', 'Publicist' )
                   then 'Gold'
              when Name in ('Inquisitive', 'Favorite Question', 'Good Question', 
                            'Notable Question', 'Enlightened', 'Refiner', 'Generalist', 
                            'Guru', 'Good Answer', 'Necromancer', 'Tenacious', 
                            'Constituent', 'Pundit', 'Enthusiast', 'Epic', 'Beta', 
                            'Convention', 'Outspoken', 'Yearling', 'Deputy', 'Civic Duty', 
                            'Reviewer', 'Strunk & White', 'Archaeologist', 'Sportsmanship', 
                            'Research Assistant', 'Taxonomist', 'Booster' )
                   then 'Silver'
              when Name in ('Altruist', 'Benefactor', 'Curious', 'Investor', 
                            'Nice Question', 'Popular Question', 'Promoter', 
                            'Scholar', 'Student', 'Tumbleweed', 'Explainer', 
                            'Nice Answer', 'Revival', 'Self-Learner', 'Teacher', 
                            'Autobiographer', 'Caucus', 'Commentator', 'Mortarboard', 
                            'Precognitive', 'Quorum', 'Talkative', 'Citizen Patrol', 
                            'Cleanup', 'Critic', 'Custodian', 'Disciplined', 
                            'Editor', 'Excavator', 'Organizer', 'Peer Pressure', 
                            'Proofreader', 'Suffrage', 'Supporter', 'Synonymizer', 
                            'Tag Editor', 'Vox Populi', 'Analytical', 'Announcer', 'Informed')
                   then 'Bronze'
              else 'Unknown'
         end as Type,
         1 as Requires
  from Badges

), BadgeSource as (

    -- Regular badges are easy, they all count for themselves.
    -- force Requires to 1

    select Name, Type as Metal, 'Regular' as Source, 1 as Requires
    from BadgeClass
    where Type <> 'Tag'
    
  Union All
    
    -- if you have 1 or more tag badge, you have the bronze
    
    select Name, 'Bronze' as Metal, 'Tag' as Source, 1 as Requires
    from BadgeClass
    where Type = 'Tag'
  
  Union All

    -- if you have 2 or more tag badge, you have the silver

    select Name, 'Silver' as Metal, 'Tag' as Source, 2 as Requires
    from BadgeClass
    where Type = 'Tag'

  Union All
    
    -- if you have 3 (cannot have more), you have the gold

    select Name, 'Gold' as  Metal, 'Tag' as Source, 3 as Requires
    from BadgeClass
    where Type = 'Tag'
    
), UserBadgeSource as (

  -- Count each badge by name, awarded to each user.

  select UserId,
         Name,
         Count (*) as BadgeCount
  from Badges
  group by UserId, Name
  
), UserBadges as (

  -- Use the Requires column to group the tags to the
  -- users data. Note that if a tag-badge has a count of 3
  -- it will join with all the tag rows in the BadgeSource CTE
  -- thus providing bronze, silver, and gold versions.

  select UserBadgeSource.UserId,
         UserBadgeSource.Name,
         BadgeSource.Metal,
         BadgeSource.Source,
         case when BadgeSource.Source = 'Tag'
              then 1
              else BadgeCount
         end as Quantity
  from UserBadgeSource
  inner join BadgeSource
         on UserBadgeSource.Name = BadgeSource.Name
        and BadgeCount >= BadgeSource.Requires
)

select UserId as [User Link],
       DisplayName as [Sort By],
       Reputation as [Reputation],
       sum(case when Metal = 'Gold' then Quantity else 0 end) as Gold,
       sum(case when Metal = 'Silver' then Quantity else 0 end) as Silver,
       sum(case when Metal = 'Bronze' then Quantity else 0 end) as Bronze,
       sum(case when Metal = 'Unknown' then Quantity else 0 end) as Unknown,
       sum(case when Source = 'Regular' then Quantity else 0 end) as Regular,
       sum(case when Source = 'Tag' then Quantity else 0 end) as Tag,
       sum(Quantity) as Total
from UserBadges inner join Users on UserId = Id
where Reputation >= ##MinReputation:int?200##
group by UserId, DisplayName, Reputation
having sum(Quantity) >= ##MinBadges:int?5##

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results