1

Please login or register to vote for this query.

(click on this box to dismiss)

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;

fork query

edited sep 16 brunolm7

created sep 5 10 leniel