Badges lead table, minus Famous Question (most bronze, silver and gold overall and for tags)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Top N users by number of badges, broken down to class,
-- with separate tagbadge counts.

-- This query filters out Famous Question, the most numerous gold badge,
-- awarded 10x as often as the next most numerous badge, Great Answer.
-- See https://data.stackexchange.com/stackoverflow/query/731539 for the
-- same data with Famous Question included.

SELECT TOP(##limit:int?25##)
  u.id as [User Link],
  COUNT(*) as Total,
  COUNT(CASE WHEN b.Class = 1 THEN 1 ELSE NULL END) as Gold,
  COUNT(CASE WHEN b.Class = 2 THEN 1 ELSE NULL END) as Silver,
  COUNT(CASE WHEN b.Class = 3 THEN 1 ELSE NULL END) as Bronze,
  COUNT(CASE WHEN b.TagBased = 1 THEN 1 ELSE NULL END) as [Total (tags)],
  COUNT(CASE WHEN b.TagBased = 1 AND b.Class = 1 THEN 1 ELSE NULL END) as [Gold (tags)],
  COUNT(CASE WHEN b.TagBased = 1 AND b.Class = 2 THEN 1 ELSE NULL END) as [Silver (tags)],
  COUNT(CASE WHEN b.TagBased = 1 AND b.Class = 3 THEN 1 ELSE NULL END) as [Bronze (tags)]
FROM Users u
INNER JOIN Badges b ON b.UserId = u.Id
WHERE b.Name != 'Famous Question'
GROUP BY u.id
ORDER BY COUNT(*) desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results