2

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows the top 100 users by count of distinct (non-tag) badges collected. The purpose of TOP 100 is because it ALSO retrieves the list of missing badges, which would be BAD to run across ALL users

Q&A for programmers

-- Distinct-badge count collected by users
-- This query shows the top 100 users by count of distinct (non-tag) badges collected

declare @badges table (
    badgename varchar(100) collate SQL_Latin1_General_CP1_CS_AS)

insert @badges
select distinct name
from badges
where not exists (
    select *
    from tags
    where tags.tagname=badges.name)

select TOP(100)
    users.id as [User Link],
    users.id,
    (select count(*) from @badges) TotalBadgeCount,
    count(earned.userId) CollectedCount,
    stuff(convert(xml,(select ','+badgename
     from @badges
     where not exists (select * from badges
                       where badges.userID=users.id
                         and badges.name=[@badges].badgename)
     for xml path(''))).value('.[1]','varchar(max)'),1,1,'') missing
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
group by users.id
order by CollectedCount desc

fork query

created feb 17 11 cyberkiwi2010