Rank users by badge count
Q&A for professional and enthusiast programmers
-- TOP Users: Brazil with Badges count -- Rank users by badge count WITH GoldBadges AS ( SELECT UserId, COUNT(*) AS c FROM Badges WHERE Class = 1 GROUP BY UserId ), SilverBadges AS ( SELECT UserId, COUNT(*) AS c FROM Badges WHERE Class = 2 GROUP BY UserId ), BronzeBadges AS ( SELECT UserId, COUNT(*) AS c FROM Badges WHERE Class = 3 GROUP BY UserId ), TopTags AS ( SELECT TagName, OwnerUserId, COUNT(Votes.Id) AS UpVotes FROM Tags INNER JOIN PostTags ON PostTags.TagId = Tags.id INNER JOIN Posts ON Posts.ParentId = PostTags.PostId INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2 GROUP BY TagName, OwnerUserId ) SELECT ROW_NUMBER() OVER(ORDER BY gb.c DESC, sb.c DESC, bb.c DESC, Reputation DESC ) AS [#], u.Id AS [User Link], u.Reputation, gb.c as GoldBadgeCount, sb.c as SilverBadgeCount, bb.c as BronzeBadgeCount, STRING_AGG(case when seqnum <= 10 then TagName end, ', ') as TopSkills FROM Users u INNER JOIN GoldBadges gb on gb.UserId = u.Id INNER JOIN SilverBadges sb on sb.UserId = u.Id INNER JOIN BronzeBadges bb on bb.UserId = u.Id INNER JOIN ( SELECT TagName, OwnerUserId, UpVotes, dense_rank() over (partition by OwnerUserId order by UpVotes DESC) as seqnum FROM TopTags ) tt on tt.OwnerUserId = u.Id WHERE LOWER(Location) LIKE '%brazil%' OR LOWER(Location) LIKE '%brasil%' OR UPPER(Location) LIKE '%BR' GROUP BY u.Id, u.Reputation, gb.c, sb.c, bb.c ORDER BY GoldBadgeCount DESC, SilverBadgeCount DESC, BronzeBadgeCount DESC, Reputation DESC;