1

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/a/252643

Q&A for speakers of other languages learning English

-- UserId: (Optional) User ID
WITH ModerationBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Moderation Badges'   , 2, 'Deputy')
        , ('Moderation Badges'   , 1, 'Marshal')
        , ('Moderation Badges'   , 2, 'Civic Duty') 
        , ('Moderation Badges'   , 3, 'Cleanup')
        , ('Moderation Badges'   , 2, 'Reviewer')
        , ('Moderation Badges'   , 1, 'Steward')
        , ('Moderation Badges'   , 1, 'Electorate')
        , ('Moderation Badges'   , 2, 'Sportsmanship')
    ) AS Badges(Category, Class, Name)
), EditingBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Answer Badges'       , 3, 'Explainer')
        , ('Answer Badges'       , 2, 'Refiner')
        , ('Moderation Badges'   , 2, 'Strunk & White')
        , ('Moderation Badges'   , 1, 'Copy Editor')
        , ('Moderation Badges'   , 3, 'Organizer')
        , ('Moderation Badges'   , 3, 'Tag Editor')
    ) AS Badges(Category, Class, Name)
), ParticipationBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Question Badges'     , 3, 'Investor')
        , ('Participation Badges', 2, 'Constituent')
        , ('Participation Badges', 2, 'Enthusiast')
        , ('Participation Badges', 3, 'Quorum')
        , ('Participation Badges', 2, 'Convention')
        , ('Participation Badges', 2, 'Yearling')
    ) AS Badges(Category, Class, Name)
), CandidateBadges AS (
    SELECT * FROM ModerationBadges
    UNION
    SELECT * FROM EditingBadges
    UNION
    SELECT * FROM ParticipationBadges
), CandidatePoints AS (
    SELECT
          Users.Id 
        , IIF(Users.Reputation > 20000, 20000, Users.Reputation) / 1000 AS ReputationPoints
        , COUNT(DISTINCT CandidateBadges.Name) AS BadgePoints
        , COUNT(DISTINCT ModerationBadges.Name) AS ModerationBadgePoints
        , COUNT(DISTINCT EditingBadges.Name) AS EditingBadgePoints
        , COUNT(DISTINCT ParticipationBadges.Name) AS ParticipationBadgePoints
        , Users.LastAccessDate
    FROM Users
    LEFT JOIN Badges
        ON Badges.TagBased = 0
        AND Users.Id = Badges.UserId
    LEFT JOIN CandidateBadges
        ON Badges.Class = CandidateBadges.Class
        AND Badges.Name = CandidateBadges.Name
    LEFT JOIN ModerationBadges
        ON Badges.Class = ModerationBadges.Class
        AND Badges.Name = ModerationBadges.Name
    LEFT JOIN EditingBadges
        ON Badges.Class = EditingBadges.Class
        AND Badges.Name = EditingBadges.Name
    LEFT JOIN ParticipationBadges
        ON Badges.Class = ParticipationBadges.Class
        AND Badges.Name = ParticipationBadges.Name
    WHERE
        ##UserId:int?0## = 0
        OR Users.Id = ##UserId:int?0##
    GROUP BY
          Users.Id
        , Users.Reputation
        , Users.LastAccessDate
)
SELECT TOP 100
      Id AS [User Link]
    , ReputationPoints + BadgePoints AS [Candidate Score]
    , ReputationPoints AS [Reputation Points]
    , BadgePoints AS [Badge Points]
    , ModerationBadgePoints AS [Moderation]
    , EditingBadgePoints AS [Editing]
    , ParticipationBadgePoints AS [Participation]
    , CONVERT(varchar, LastAccessDate, 23) AS [Last Seen]
FROM CandidatePoints
ORDER BY
      [Candidate Score] DESC
    , [Last Seen] DESC
    , Id ASC

view current version

edited sep 10 Gao

created apr 8 15 anonymous