0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

Select Username as [User Link], CappedRep+BadgeCount as "Total/40", CappedRep "Reputation/20", BadgeCount "Total Badges/20", ModBadges "Moderation badges/8", EdBadges "Editing Badges/6", ParticipationBadges "Participation Badges/6"
From (
   Select u.Id As Username, 
     (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
   Where u.Id in (##UserIds##)
  Group By u.Id, u.Reputation
) cscores

Order By "Total/40" desc

-- UserIds: User IDs "Enter IDs separated by commas"

-- Latin users with score at least 1000 in April 2018:
-- 79,78,45,68,40,645,30,406,324,12,985,118,39,235,867,9,469,522,136,73,71,932,1694,1148,37,1459,952,75,1289,1861,20,1224,66

fork query

edited sep 27 22

created sep 27 22 anonymous