Citizenship score based on certain badges and reputation

4

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

With prelim As 
(
   Select u.Id As Username, 
      floor(10+0.5*u.Reputation/1000-0.5*abs(u.Reputation/1000-20)) As CappedRep, 
      Count(distinct b.Name) As BadgeCount, 
      u.LastAccessDate As LastAccess 
   From Users u
   Inner Join Badges b On b.UserId = u.Id
   Where u.Reputation >= 1000 and 
     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')
  Group By u.Id, u.Reputation, u.LastAccessDate
) 
  
Select Username as [User Link], CappedRep+BadgeCount as Total, CappedRep, BadgeCount, LastAccess 
From prelim 
Order By Total desc

Enter Parameters

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