0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for SharePoint enthusiasts

WITH ModerationBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Civic Duty') 
        , ('Cleanup')
        , ('Deputy')
        , ('Electorate')
        , ('Marshal')
        , ('Reviewer')
        , ('Sportsmanship')
        , ('Steward')
    ) AS Badges(Name)
), EditingBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Copy Editor')
        , ('Explainer')
        , ('Organizer')
        , ('Refiner')
        , ('Strunk & White')
        , ('Tag Editor')
    ) AS Badges(Name)
), ParticipationBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Constituent')
        , ('Convention')
        , ('Enthusiast')
        , ('Investor')
        , ('Quorum')
        , ('Yearling')
    ) AS Badges(Name)
), CandidateBadges AS (
    SELECT Name FROM ModerationBadges
    UNION
    SELECT Name FROM EditingBadges
    UNION
    SELECT Name FROM ParticipationBadges
), CandidateScore AS (
    SELECT
          Users.Id 
        , Users.Reputation
        , IIF(Users.Reputation > 20000, 20000, Users.Reputation) / 1000 AS CappedReputation
        , COUNT(DISTINCT CandidateBadges.Name) AS CandidateBadgeCount
        , COUNT(DISTINCT ModerationBadges.Name) AS ModerationBadgeCount
        , COUNT(DISTINCT EditingBadges.Name) AS EditingBadgeCount
        , COUNT(DISTINCT ParticipationBadges.Name) AS ParticipationBadgeCount
        , Users.LastAccessDate
    FROM Users
    LEFT JOIN Badges
        ON Users.Id = Badges.UserId
    LEFT JOIN CandidateBadges
        ON Badges.Name = CandidateBadges.Name
    LEFT JOIN ModerationBadges
        ON Badges.Name = ModerationBadges.Name
    LEFT JOIN EditingBadges
        ON Badges.Name = EditingBadges.Name
    LEFT JOIN ParticipationBadges
        ON Badges.Name = ParticipationBadges.Name
    WHERE
        -- UserId: (Optional) 
        TRIM(COALESCE(##UserId:string##, '')) = ''
        OR Users.Id = ##UserId:string##
    GROUP BY
          Users.Id
        , Users.Reputation
        , Users.LastAccessDate
)
SELECT TOP 100
      Id AS [User Link]
    , CappedReputation + CandidateBadgeCount AS [Candidate Score]
    , CappedReputation AS [Capped Reputation]
    , CandidateBadgeCount AS [Candidate Badges]
    , ModerationBadgeCount AS [Moderation Badges]
    , EditingBadgeCount AS [Editing Badges]
    , ParticipationBadgeCount AS [Participation Badges]
FROM CandidateScore
ORDER BY
      [Candidate Score] DESC
    , LastAccessDate DESC

fork query

edited sep 10 Gao

created apr 8 15 anonymous