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 StarBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Moderation'   , 'Moderation Badges'   , 2, 'Deputy')
        , ('Moderation'   , 'Moderation Badges'   , 1, 'Marshal')
        , ('Moderation'   , 'Moderation Badges'   , 2, 'Civic Duty') 
        , ('Moderation'   , 'Moderation Badges'   , 3, 'Cleanup')
        , ('Moderation'   , 'Moderation Badges'   , 2, 'Reviewer')
        , ('Moderation'   , 'Moderation Badges'   , 1, 'Steward')
        , ('Moderation'   , 'Moderation Badges'   , 1, 'Electorate')
        , ('Moderation'   , 'Moderation Badges'   , 2, 'Sportsmanship')
        , ('Editing'      , 'Answer Badges'       , 3, 'Explainer')
        , ('Editing'      , 'Answer Badges'       , 2, 'Refiner')
        , ('Editing'      , 'Moderation Badges'   , 2, 'Strunk & White')
        , ('Editing'      , 'Moderation Badges'   , 1, 'Copy Editor')
        , ('Editing'      , 'Moderation Badges'   , 3, 'Organizer')
        , ('Editing'      , 'Moderation Badges'   , 3, 'Tag Editor')
        , ('Participation', 'Question Badges'     , 3, 'Investor')
        , ('Participation', 'Participation Badges', 2, 'Constituent')
        , ('Participation', 'Participation Badges', 2, 'Enthusiast')
        , ('Participation', 'Participation Badges', 3, 'Quorum')
        , ('Participation', 'Participation Badges', 2, 'Convention')
        , ('Participation', 'Participation Badges', 2, 'Yearling')
    ) AS Badges(Classification, Category, Class, Name)
), CandidatePoints AS (
    SELECT
          Users.Id 
        , IIF(Users.Reputation > 20000, 20000, Users.Reputation) / 1000 AS ReputationPoints
        , COUNT(DISTINCT IIF(StarBadges.Classification = 'Moderation', StarBadges.Name, NULL)) AS ModerationBadgePoints
        , COUNT(DISTINCT IIF(StarBadges.Classification = 'Editing', StarBadges.Name, NULL)) AS EditingBadgePoints
        , COUNT(DISTINCT IIF(StarBadges.Classification = 'Participation', StarBadges.Name, NULL)) AS ParticipationBadgePoints
        , Users.LastAccessDate
    FROM Users
    LEFT JOIN Badges
        ON Badges.TagBased = 0
        AND Users.Id = Badges.UserId
    LEFT JOIN StarBadges
        ON Badges.Class = StarBadges.Class
        AND Badges.Name = StarBadges.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 
        + ModerationBadgePoints 
        + EditingBadgePoints 
        + ParticipationBadgePoints
      AS [Candidate Score]
    , ReputationPoints AS [Reputation Points]
    , ModerationBadgePoints AS [Moderation Points]
    , EditingBadgePoints AS [Editing Points]
    , ParticipationBadgePoints AS [Participation Points]
    , 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