Users with highest candidate score

2

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

;  with score_cte as
(
Select u.Id As uid,
     (case when u.Reputation > 20000 then 20000 else u.Reputation end)/1000 As CappedRep,
      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
   Group By u.Id, u.Reputation
)
--the above is taken from https://data.stackexchange.com/math/query/298418/candidate-score

SELECT TOP(##num?100##)
  uid AS [User Link], CappedRep+BadgeCount as Total, CappedRep, BadgeCount, ModBadges, EdBadges "EditingBadges", ParticipationBadges
FROM score_cte
ORDER BY Total DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results