Tag badges you were first to earn

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

-- Tag Badges you earned before anyone else
-- Gives totals, and per name what class you were first to earn

WITH badges_first AS (
  SELECT
    Class,
    Name,
    UserId,
    ROW_NUMBER() OVER(PARTITION BY Class, Name 
                      ORDER BY Date ASC) AS rk
 
  FROM Badges
  WHERE TagBased = 1)

SELECT
  CASE WHEN (GROUPING(Name) = 1) THEN 'TOTAL' ELSE Name END as TagName,
  COUNT(CASE WHEN Class = 1 THEN 1 ELSE NULL END) as Gold,
  COUNT(CASE WHEN Class = 2 THEN 1 ELSE NULL END) as Silver,
  COUNT(CASE WHEN Class = 3 THEN 1 ELSE NULL END) as Bronze
FROM badges_first
WHERE 
  rk = 1 AND
  UserId = ##UserId##
GROUP BY Name WITH ROLLUP
ORDER BY Name

Enter Parameters

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