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.

Q&A for those who study, teach, research and apply economics and econometrics

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

fork query

created may 5 11 mattdm