Silver tag badge holders

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

-- Identify tag badges as the badges whose name doesn't start with an uppercase letter.
-- Identify silver tag badges because the same user has two badges by that name.
-- http://meta.stackexchange.com/questions/148147/can-we-expose-class-for-badges-in-data-explorer

-- FIXME: gold badge holders are shown in triplicate, rather than shown with True in the [Gold] column as I would like.

SELECT
  'site://tags/' + b.Name + '/info|' + b.Name AS [Tag],
  u.Id AS [User Link],
--  (EXISTS (SELECT bbb.Id FROM Badges bbb
--           WHERE bbb.UserId = b.UserId AND bbb.Name = b.Name
--             AND bbb.Id > bb.Id)) AS [Gold],
  u.Reputation AS [Rep],
  u.UpVotes + u.DownVotes AS [Votes]
FROM Badges b
INNER JOIN Users u ON u.Id = b.UserId
RIGHT JOIN Badges bb ON bb.UserId = b.UserId
                    AND bb.Name = b.Name
                    AND bb.Id > b.Id
WHERE b.Name NOT LIKE '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
ORDER BY b.Name ASC, u.Reputation DESC, u.Id ASC

Enter Parameters

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