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

0

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 table is skewed somewhat by the huge number of Famous Question
-- gold badges (awarded 10x as many times as the next popular gold badge,
-- Great Answer). See https://data.stackexchange.com/stackoverflow/query/731572
-- for the same query, but filtering out Famous Question.

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
GROUP BY u.id
ORDER BY COUNT(*) desc

Enter Parameters

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