0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those using, extending or developing Emacs

-- 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 case statement can then be used to determine the ranks of each badge.

    select 
		Badges.Name,
		coalesce(TagLookup.BadgeRank,Badges.Type) as Type,
		case when Badges.Type = 'Tag' 
			 then 'Tag'
			 else 'Regular'
		end as Source,
		Badges.BadgeNumber as Requires
	from 
	(
		select 
			Name,
			Type,
			Requires,
			row_number() over (partition by Name order by (select 0)) as BadgeNumber
		from BadgeClass
	) as Badges
	left join
	(
		select 1 as BadgeNumber, 'Bronze' as BadgeRank union all
		select 2 as BadgeNumber, 'Silver' as BadgeRank union all
		select 3 as BadgeNumber, 'Gold' as BadgeRank
	) as TagLookup
		on Badges.Type = 'Tag'
		
    
)

SELECT * FROM BadgeSource

fork query

edited dec 16 14 anonymous

created dec 12 14 rolfl