Distinct-badge count collected by users

0

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

Vegetarianism

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

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

select TOP(100)
    users.id as [User Link],
    users.id,
    count(*) TotalBadgeCount,
    count(earned.userId) CollectedCount
from users
cross join
(
    select distinct name badgename
    from badges
    where not exists (
        select *
        from tags
        where tags.tagname=badges.name)
) 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

Enter Parameters

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