Gold, Silver and Bronze Tag Badges

2

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

WITH TagBadges AS (
  SELECT
    ROW_NUMBER() OVER(ORDER BY AwardCount DESC, COUNT(*) DESC) AS Record,
    UserId,
    COUNT(*) AS AwardCount,
    AwardCount AS BadgeLevel
  FROM
  (
    SELECT
      UserId,
      COUNT(*) AS AwardCount
    FROM
      Badges
    JOIN
      Tags ON Badges.Name = Tags.TagName
    GROUP BY
      UserId, Tags.TagName
  ) AS TagBadges
  GROUP BY UserId, AwardCount
)

SELECT
  UserId AS [User Link],
  CASE WHEN TagBadges.BadgeLevel = 3 THEN 'Gold'
       WHEN TagBadges.BadgeLevel = 2 THEN 'Silver'
       ELSE 'Bronze'
  END AS [Badge Level],
  AwardCount AS [Badge Count]
FROM
  TagBadges
JOIN
  (
    SELECT
      CASE WHEN MAX(Record) - MIN(Record) < 10 THEN MAX(Record) - MIN(Record)
           ELSE MIN(Record) + 9
      END AS UpperLimit
      , BadgeLevel
    FROM
      TagBadges
    GROUP BY BadgeLevel
  ) AS Partitions ON TagBadges.BadgeLevel = Partitions.BadgeLevel
WHERE
  TagBadges.Record <= Partitions.UpperLimit
ORDER BY
  TagBadges.BadgeLevel DESC, AwardCount DESC

Enter Parameters

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