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 
        Name,
        case when Type = 'Tag' and BadgeNumber = 3
             then 'Gold'
             when Type = 'Tag' and BadgeNumber = 2
             then 'Silver'
             when Type = 'Tag' and BadgeNumber = 1
             then 'Bronze'
             when Type <> 'Tag'
             then Type        
        end as Metal,
        case when Type = 'Tag' 
             then 'Tag'
             else 'Regular'
        end as Source,
        case when Type = 'Tag'
             then BadgeNumber
             when Type = 'Tag' 
             then BadgeNumber
             when Type = 'Tag'
             then BadgeNumber
             when Type <> 'Tag'
             then BadgeNumber
        end as Requires
    from 
    (
        select 
            Name,
            Type,
            Requires,
            row_number() over (partition by Name order by (select 0)) as BadgeNumber
        from BadgeClass
    ) as Badges
    
)

SELECT * FROM BadgeSource

fork query

edited dec 16 14 anonymous

created dec 12 14 rolfl