Users and Badges

0

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

-- 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 (

  --We number the badges that people have using the row_number function,
  --the specific order we give them is not important, so I used select 0 to order the number in whatever order they are returned.
  --A simple lookup table is joined to these numbers if the badge is a tag badge to swap the number for the appropiate rank.

    select 
		Badges.Name,
		coalesce(TagLookup.Rank,Badges.Type) as Metal,
		case when Badges.Type = 'Tag' 
			 then 'Tag'
			 else 'Regular'
		end as Source,
		coalesce(TagLookup.Requires,1) as Requires
	from BadgeClass as Badges
	left join
	(
		select 1 as Requires, 'Bronze' as Rank union all
		select 2 as Requires, 'Silver' as Rank union all
		select 3 as Requires, 'Gold' as Rank
	) as TagLookup
		on Badges.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 main site
loading Hold tight while we fetch your results