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 ), CandidateScore 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 CandidateScore ORDER BY [Candidate Score] DESC , LastAccessDate DESC