Bling Accounting - Badge summaries

A question was raised in chat (hi @malachi) about how you can count the number of badges of a given metal (bronze, silver, gold). This can be hard to do because the medal details are not stored in the SEDE tables. Together with Malachi, we came up with this Users and Badges query:

```
-- 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',

Style Points

Okay, first things first. you pretty consistently used lowercase throughout. You left a LOWER() in UPPER at the top of the query that I feel compelled to point out to you.

Other than that, you get style points for consistent, easy to read SQL. I personally would have written it in SHOUTCASE but as SQL has no real standards to speak of that is mostly a personal preference.
Improvements

The BadgeSource CTE is doing more work than it needs to. Here is an alternate way to achieve the same using the lookup table.

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'

Okay, This is probably easier to understand than my initial approach. This just joins the BadgeClass to a lookup table, if the record is a tag and relies on the fact that because I did not provide a column to join using it will join the entire lookup table to each tag badge record..

The benefit of doing it this way is that you get to avoid all the extra overhead that comes with the multiple union alls in the original. That means we end up with a much nicer execution plan, and a much faster query. You can try the Updated Users and Badges Query Here
Overall

That's a pretty nice query overall, you wrote clear, consistent, easy to understand SQL. Well done.

StackExchange Code Review Q#73819, answer score: 5