0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Enter Query Title
-- Enter Query Description

DECLARE @badges TABLE (
    badgename varchar(100) collate SQL_Latin1_General_CP1_CS_AS,
    badgecount int,
    badgeweight float)

INSERT @badges
SELECT DISTINCT name AS badgename, count(name) AS badgecount, (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) / count(Name) as badgeweight
FROM badges
WHERE NOT EXISTS (SELECT * FROM tags WHERE tags.tagname=badges.name)
GROUP BY name

SELECT users.id as [User Link], count(earned.userId) Badges
FROM users
CROSS JOIN @badges
LEFT JOIN
(
    SELECT name badgename, userId
    FROM badges
    GROUP BY name, userId
) earned ON earned.badgename = [@badges].badgename AND users.id = earned.userId
WHERE reputation > 101
GROUP BY users.id
ORDER BY badges DESC

fork query

created may 5 11 mattdm