What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 128 Stack Exchange communities.

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.)

share|improve this question
3  
I'm also unclear why there aren't separate tables for badge definitions and assignments in the exported data... –  Tim Stone Sep 24 '12 at 13:36
1  
It's a great flaw in DataExplorer design. Why on hell so little upvotes? –  Donaudampfschifffreizeitfahrt Jul 22 '13 at 16:38
    
@ŁukaszLech and even a downvote on both the question and the answer, which I suspect is someone just being un-community-like rather than being opposed to the ideas here. –  Aaron Bertrand Jul 22 '13 at 17:07
    
@ŁukaszLech as for few upvotes, maybe data explorer isn't as popular as we'd like it to be, or people just don't care about it in the context of researching badges. –  Aaron Bertrand Jul 22 '13 at 17:21

1 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).

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

share|improve this answer
    
In general case this code even doesn't work. If the new badge is added, you don't know what class it is. –  Donaudampfschifffreizeitfahrt Jul 22 '13 at 16:38
    
@ŁukaszLech well, I didn't intend to write a predictive query that reads the community owners' minds. If a new badge is added, you'll have to adjust the query to accommodate. Not that new badges are all that common anyway... –  Aaron Bertrand Jul 22 '13 at 17:06
    
It's another argument for considering the lack of badge classes in data explorer a design flaw –  Donaudampfschifffreizeitfahrt Jul 22 '13 at 17:10
    
@ŁukaszLech Oh I fully agree with Tim's comment above. Why this is fuzzy logic is beyond me. –  Aaron Bertrand Jul 22 '13 at 17:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .