Users by Weighted Badge Score

4

Please login or register to vote for this query.

(click on this box to dismiss)

Some badges are commonplace, and others are hard to get. This uses the relative "weight" of each badge (http://data.stackexchange.com/photography/s/1306/badges-to-aspire-to) to rank users on badges earned.

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

-- Users by Weighted Badge Score
-- Some badges are commonplace, and others are hard to get. This uses the relative "weight" of each badge  (http://data.stackexchange.com/photography/s/1306/badges-to-aspire-to) to rank users on badges earned.


DECLARE @badgeweight TABLE (
    name varchar(100) collate SQL_Latin1_General_CP1_CS_AS,
    weight float)



INSERT @badgeweight
SELECT name, ((SELECT TOP 1 count(Name) FROM Badges WHERE NOT EXISTS (SELECT * FROM tags WHERE tags.tagname=badges.name) GROUP BY name ORDER BY count(Name) DESC)+0.0) / count(Name) as weight
FROM Badges
WHERE NOT EXISTS (SELECT * FROM tags WHERE tags.tagname=badges.name)
GROUP BY name

SELECT TOP 100 Badges.UserId as [User Link], count(*) as Badges, round(sum(weight),0) as [Weighted Badge Score]
FROM @badgeweight AS w
JOIN Badges ON w.name = Badges.name
GROUP BY Badges.UserId
ORDER BY [Weighted Badge Score] DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results