Moderator Election Score calculation as per http://meta.stackexchange.com/a/247138/260340 and http://meta.stackexchange.com/a/252643/260340 The score is made up of up to 20 points for each 1k reputation, plus 20 badges.
Вопросы и ответы для программистов
--MinRep: Minimum reputation ;with eligible as ( SELECT DISTINCT b1.UserID FROM Badges b1 INNER JOIN Badges b2 on b1.UserID = b2.UserID INNER JOIN Badges b3 on b1.UserID = b3.UserID INNER JOIN Badges b4 on b1.UserID = b4.UserID WHERE b1.Name = 'Civic Duty' AND b2.Name = 'Convention' AND b3.Name = 'Strunk & White' AND b4.Name = 'Deputy' ) Select Username as [User Link], CappedRep+BadgeCount as Total, Reputation, CappedRep, BadgeCount, ModBadges, EdBadges "EditingBadges", ParticipationBadges From ( Select u.Id As Username, (case when u.Reputation > 20000 then 20000 else u.Reputation end)/1000 As CappedRep, u.Reputation as Reputation, Count(distinct case when b.Name in ( 'Civic Duty', 'Cleanup', 'Constituent', 'Convention', 'Copy Editor', 'Deputy', 'Electorate', 'Enthusiast', 'Explainer', 'Investor', 'Marshal', 'Organizer', 'Quorum', 'Refiner', 'Sportsmanship', 'Strunk & White', 'Tag Editor', 'Reviewer', 'Steward', 'Yearling') then b.Name else null end) As BadgeCount, count(distinct iif(b.Name in ('Civic Duty', 'Cleanup', 'Deputy', 'Electorate', 'Marshal', 'Sportsmanship', 'Reviewer', 'Steward'), b.Name, null)) ModBadges, count(distinct iif(b.Name in ( 'Constituent', 'Convention', 'Enthusiast', 'Investor', 'Quorum', 'Yearling'), b.Name, null)) ParticipationBadges, count(distinct iif(b.Name in ( 'Organizer', 'Copy Editor', 'Explainer', 'Refiner', 'Tag Editor', 'Strunk & White'), b.Name, null)) EdBadges From Users u left join Badges b on b.UserId=u.Id Where u.Id in (SELECT UserId FROM Users WHERE Users.Reputation > = ##MinRep:int?300## AND u.Id in (select UserID from eligible) ) Group By u.Id, u.Reputation ) cscores WHERE CappedRep+BadgeCount > 0 AND Reputation > = ##MinRep:int?300## Order By Total desc