1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @now DATETIME =
  DATEADD(s, 1, (SELECT MAX(LastAccessDate) FROM Users));

WITH Raw AS (
  SELECT
    Users.Id AS UserId,
    COUNT(CASE WHEN Badges.Name = 'Custodian' THEN 1 END)
      AS Custodian,
    COUNT(CASE WHEN Badges.Name = 'Reviewer' THEN 1 END)
      AS Reviewer,
    COUNT(CASE WHEN Badges.Name = 'Steward' THEN 1 END)
      AS Steward,
    MIN(Users.Reputation) AS Reputation,
    MIN(Users.LastAccessDate) AS LastAccessDate
  FROM Badges, Users
  WHERE
    Badges.UserId = Users.Id
    AND
    Badges.Name IN ('Custodian', 'Reviewer', 'Steward')
    AND
    Users.Reputation < ##Rep:int?2000##
    AND
    Users.Id IN (
      SELECT Users.Id
      FROM Users, Posts
      WHERE
        Users.Id = Posts.OwnerUserId
        AND
        Posts.CommunityOwnedDate IS NULL
        AND
        Users.Reputation < ##Rep##
      GROUP BY Users.Id
      HAVING MIN(Users.Reputation) + COUNT(*) * 10 >= ##Rep##
    )
  GROUP BY Users.Id
)
SELECT
  UserId AS [User Link],
  Custodian,
  Reviewer,
  Steward,
  Reputation,
  LastAccessDate,
  1.0 * (Custodian + Reviewer * 250 + Steward * 1000)
    / ((##Rep## + 9 - Reputation) / 10)
    / (DATEDIFF(d, LastAccessDate, @now) + 1)
    AS Goodness
FROM Raw
ORDER BY Goodness DESC, UserId

fork query

edited jan 23

created jan 8 peterh - PLEASE DELETE THIS ACCOUNT