1

Please login or register to vote for this query.

(click on this box to dismiss)

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.

Q&A for программистов

--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

fork query

edited sep 26 15 PashaPash

created may 4 15 anonymous