0

Please login or register to vote for this query.

(click on this box to dismiss)

Lists all earners of badges for a particular tag, sorted by how long the user took to earn the badge. Badges are now classified as Bronze, Silver, or Gold. Also computes DaysSince1st - the number of days between the earliest occurrence of the badge and the user's award date. DaysSince1st is computed independently for each class.

Q&A for scientists using computers to solve scientific problems

-- Quickest badge earners V3 (derived from V2c)
-- Classify badge as Bronze, Silver, or Gold
-- Compute DaysSince1st for each class independently
with
BadgeEarners as (
 select u.Id as userId,
        u.CreationDate as memberSince,
        rank() over(partition by u.Id order by b.Date) as typeRank,
        b.Date as dateWon,
        1+DateDiff(day, u.CreationDate, b.Date) as daysMembership
   from badges b
   join users u
     on b.UserId = u.Id
  where b.Name = '##BadgeName##'
),
FirstEarned as (
 select typeRank,
        min(dateWon) firstWon
   from BadgeEarners
  group by typeRank
)
select case be.typeRank
            when 1 then 'Bronze'
            when 2 then 'Silver'
            when 3 then 'Gold'
            else 'Error'
        end as [Type],
       be.userId as [User Link],
       be.memberSince as [Member Since],
       be.dateWon as [Date Won],
       be.daysMembership as [DaysMembership],
       DateDiff(Day, firstWon, dateWon) as [DaysSince1st]
  from BadgeEarners be
  join FirstEarned fe
    on be.typeRank=fe.typeRank
 order by be.typeRank desc, be.daysMembership asc

fork query

edited feb 16 13 dbenham

created sep 5 10 polygenelubricants