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.
case when LOWER(Name) = Name
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' )
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' )
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')
end as Type,
1 as Requires
), 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.
coalesce(TagLookup.BadgeRank,Badges.Type) as Metal,
case when Badges.Type = 'Tag'
end as Source,
coalesce(TagLookup.Requires,1) as Requires
from BadgeClass as Badges
select 1 as Requires, 'Bronze' as BadgeRank union all
select 2 as Requires, 'Silver' as BadgeRank union all
select 3 as Requires, 'Gold' as BadgeRank
) as TagLookup
on Badges.Type = 'Tag'
), UserBadgeSource as (
-- Count each badge by name, awarded to each user.
Count (*) as BadgeCount
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.
case when BadgeSource.Source = 'Tag'
end as Quantity
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##