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(Badge)
), EditingBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Copy Editor')
        , ('Explainer')
        , ('Organizer')
        , ('Refiner')
        , ('Strunk & White')
        , ('Tag Editor')
    ) AS Badges(Badge)
), ParticipationBadges AS (
    SELECT DISTINCT *
    FROM (
        VALUES
          ('Constituent')
        , ('Convention')
        , ('Enthusiast')
        , ('Investor')
        , ('Quorum')
        , ('Yearling')
    ) AS Badges(Badge)
), CandidateBadges AS (
    SELECT * FROM ModerationBadges
    UNION
    SELECT * FROM EditingBadges
    UNION
    SELECT * FROM ParticipationBadges
), CandidateScore AS (
    SELECT
          Users.Id 
        , Users.Reputation
        , IIF(Users.Reputation > 20000, 20000, Users.Reputation) / 1000 AS CappedReputation
        , (
            SELECT COUNT(DISTINCT Badge)
            FROM (
                SELECT * FROM CandidateBadges WHERE Badge = Badges.Name
            ) AS BadgeCount
        ) AS CandidateBadgeCount
        , (
            SELECT COUNT(DISTINCT Badge)
            FROM (
                SELECT * FROM ModerationBadges WHERE Badge = Badges.Name
            ) AS BadgeCount
        ) AS ModerationBadgeCount
        , (
            SELECT COUNT(DISTINCT Badge)
            FROM (
                SELECT * FROM EditingBadges WHERE Badge = Badges.Name
            ) AS BadgeCount
        ) AS EditingBadgeCount
        , (
            SELECT COUNT(DISTINCT Badge)
            FROM (
                SELECT * FROM ParticipationBadges WHERE Badge = Badges.Name
            ) AS BadgeCount
        ) AS ParticipationBadgeCount
        --, COUNT(DISTINCT IIF(EXISTS (SELECT * FROM CandidateBadges WHERE Badge = Badges.Name), Badges.Name, NULL)) AS CandidateBadgeCount
        --, COUNT(DISTINCT IIF(EXISTS (SELECT * FROM ModerationBadges WHERE Badge = Badges.Name), Badges.Name, NULL)) AS ModerationBadgeCount
        --, COUNT(DISTINCT IIF(EXISTS (SELECT * FROM EditingBadges WHERE Badge = Badges.Name), Badges.Name, NULL)) AS EditingBadgeCount
        --, COUNT(DISTINCT IIF(EXISTS (SELECT * FROM ParticipationBadges WHERE Badge = Badges.Name), Badges.Name, NULL)) AS ParticipationBadgeCount
        , Users.LastAccessDate
    FROM Users
    LEFT JOIN Badges
        ON Users.Id = Badges.UserId
    WHERE
        -- UserIds: (Optional) comma-separated list of user IDs
        COALESCE(##UserIds##, '') = ''
        OR Users.Id IN (##UserIds##)
    GROUP BY
    Badges.Name
    ,      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