0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

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
      MIN(Record) AS Start, BadgeLevel
    FROM
      TagBadges
    GROUP BY BadgeLevel
  ) AS Partitions ON TagBadges.BadgeLevel = Partitions.BadgeLevel
WHERE
  TagBadges.Record <= Partitions.Start + 9
ORDER BY
  TagBadges.BadgeLevel DESC, AwardCount DESC

fork query

edited may 1 12 Mark Hurd

created may 1 12 Tim Stone