0

Please login or register to vote for this query.

(click on this box to dismiss)

Now addresses the fact that some badges aren't even introduced until a certain date, so some members couldn't have gotten it any sooner since they aren't applied retroactively. Added 1 plus DateDiff (OBOE)

Q&A for scientists using computers to solve scientific problems

-- Quickest badge earners V2b
-- Now addresses the fact that some badges aren't even introduced until a certain date, so some members couldn't have gotten it any sooner since they aren't applied retroactively.
-- Added 1 plus DateDiff (OBOE)

DECLARE @firstTime date
SELECT @firstTime = min(Badges.Date) FROM Badges WHERE Badges.Name = '##BadgeName##'
;
WITH BadgeEarners AS (
   SELECT
      Users.Id as [User Link],
      Users.CreationDate as [Member Since],
      rank() over(partition by Users.Id order by Badges.Date) as [TypeRank],
      Badges.Date as [Date Won],
      1+DateDiff(Day, Users.CreationDate, Badges.Date) As [DaysMembership]
   FROM
     Badges
     INNER JOIN Users
     ON Badges.UserId = Users.Id
   WHERE
     Badges.Name = '##BadgeName##'
)

SELECT
   [User Link],
   [Member Since],
   case [TypeRank] when 1 then 'Bronze' when 2 then 'Silver' when 3 then 'Gold' end as [Type], 
   [Date Won],
   [DaysMembership],
   DateDiff(Day, @firstTime, [Date Won]) As [DaysSince1st]
FROM BadgeEarners
ORDER BY
   [TypeRank] DESC, [DaysMembership] ASC

fork query

edited feb 16 13 dbenham

created sep 5 10 polygenelubricants