0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

/* 
since we know that:
   - tag-related badges are lower case
   - to have a gold tag-related badge you must have all three
*/

;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;

/* and for non-tag-related, we currently need the names */

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;

fork query

edited sep 24

created sep 24 anonymous