Can we expose class for badges in Data Explorer?

Question

According to this answer the following metadata is exposed for badges:

Id
UserId
Name
Date

Why isn't class exposed in some way? It's obviously in the schema, since we can sort / filter in the UI. Come to think of it the description might be something useful to filter on in a query as well, e.g.:

Give me the name and count for all gold badges that contain the word 'vote' in the description.

I am not sure if class is exposed in a different way for tag-related badges (I suspect the schema is more complex, since their existence relies on them having been awarded in that class at least once).

(Also, the Id in the Badges table is not the badge id, it seems to be an IDENTITY / auto-increment column.)

Answer 1

To demonstrate why not having class makes searching the data complex (and in case anyone out there is trying to figure out these queries). Keeping in mind that Data Explorer is significantly out of date.

(1) tag-related gold badges

Since we know that:

• tag-related badges are lower case
• a badge for a tag at a certain level only exists when it's been awarded once
• to have a gold tag-related badge you must have all three (bronze, silver, gold)
• a tag-related badge can only be awarded to each user once

;WITH x AS 
(
  SELECT Name, UserId, c = count(*)
   FROM dbo.Badges
   WHERE ASCII(LEFT(Name, 1)) BETWEEN 97 AND 122
   GROUP BY Name, UserId
   HAVING COUNT(*) = 3 
   -- for silver, >= 2
   -- for bronze, >= 1
)
SELECT Name, [Count] = COUNT(*) 
FROM x
GROUP BY Name
ORDER BY [Count] DESC;

(2) For non-tag-related gold badges, we currently need to know the names. This query shows the number of users who received each badge at least once, and also the total number of times the badge has been awarded.

SELECT 
  Name, 
  [User Count] = COUNT(DISTINCT UserId), 
  [Total] = COUNT(UserId)
FROM dbo.Badges 
WHERE Name IN 
(
 N'Copy Editor',
 N'Electorate',
 N'Famous Question',
 N'Fanatic',
 N'Great Answer',
 N'Great Question',
 N'Legendary',
 N'Marshal',
 N'Populist',
 N'Publicist',
 N'Reversal',
 N'Stellar Question',
 N'Steward',
 N'Unsung Hero'
)
GROUP BY Name
ORDER BY [User Count] DESC;

Feel free to fork these queries if you want to discover other information (like who all those people are).

https://data.stackexchange.com/stackoverflow/revision/80698/92524/gold-badge-counts

Answer 2

This was apparently implemented at some point in time - the Badges table now has two new columns: Class, which is a tinyint, and TagBased, which is a bit.

Class is 3 for bronze badges; 2 for silver badges; and 1 for gold badges.

TagBased is, of course, true for tag badges and false otherwise.

There isn't a helper table (BadgeClasses, say) to tell you what the Class numbers mean, alas.