10

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

-- Legendary Badge advancement
-- Retrieve details on the race to legendary for all site users.


SELECT ROW_NUMBER() OVER (ORDER BY Count(prog.DailyRep) DESC) AS [Rank],
       prog.UserId as [User Link],
       Count(prog.DailyRep) as [Progress]
FROM (
  SELECT
      UserId AS [UserId],
      Date AS [Date],
      SUM(RepChange) AS [DailyRep]
  FROM (
      SELECT
          Posts.OwnerUserId AS UserId,
          CASE VoteTypeId
              WHEN 1 THEN 15
              WHEN 2 THEN 10
              WHEN 3 THEN -2
              WHEN 8 THEN -BountyAmount
              WHEN 9 THEN BountyAmount
              WHEN 16 THEN 2
              END RepChange,
          Votes.CreationDate AS Date
      FROM Votes
          INNER JOIN Posts ON Posts.Id = Votes.PostId
          INNER JOIN Users ON Users.Id = Posts.OwnerUserId
      WHERE VoteTypeId IN (1,2,3,8,9,16)
          AND Posts.CommunityOwnedDate IS NULL
  ) sq
  GROUP BY
      UserId, Date
) prog
WHERE DailyRep >= 200
GROUP BY UserId
Order By [Progress] Desc

fork query

edited jan 19 14 rolfl

created jan 2 14 zessx