2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with answer_edits as (
  select UserId, PostId as [QuestionId], a.Id as [AnswerId]
  from Posts q
       join PostHistory e on PostId = q.Id
       join Posts a on a.ParentId = q.Id
                       and IsNull(q.OwnerUserId, -1) <> a.OwnerUserId
                       and UserId = a.OwnerUserId
                       and abs(datediff(hour, a.CreationDate, e.CreationDate))
                           < 12
                           
  where PostHistoryTypeId in (4, 5) -- Edit title, body, (not) tags
        and q.PostTypeId = 1
        and a.PostTypeId = 2
        and q.ClosedDate is null
        and a.Score >= 1
),

curator_badges as (
  select UserId,
         count(*) as [AnswerCount],
         count(distinct QuestionId) as [QuestionCount],
         case when count(distinct QuestionId) >=   1 then 1 else 0 end bronze,
         case when count(distinct QuestionId) >=  50 then 1 else 0 end silver,
         case when count(distinct QuestionId) >= 500 then 1 else 0 end gold
  from answer_edits
  group by UserId
),

ranking as (
    select userId,
           Rank () over (order by QuestionCount desc) as [Ranking],
           QuestionCount,
           case
                when gold = 1 then 'Illuminator'
                when silver = 1 then 'Refiner'
                when bronze = 1 then 'Explainer'
           else ''
           end as [BadgeExpect]
    from curator_badges
)
/*
,
awarded as (
    select Best.UserId, Best.Name as Awarded
    from Badges Best
    where Best.Name in ('Illuminator', 'Refiner', 'Explainer')
      and (    (     Best.Name = 'Refiner'
                 and not exists (select 1 from Badges where UserId = Best.UserId and Name = 'Illuminator')
                )
           or (     Best.Name = 'Explainer'
                 and not exists (select 1 from Badges where UserId = Best.UserId and Name = 'Refiner')
                )
          )
),
interesting as (
    select UserId from awarded
    UNION
    select UserId from ranking
)

select Id as [User Link],
       DisplayName,
       Ranking,
       QuestionCount,
       BadgeExpect,
       Awarded,
       case when BadgeExpect = Awarded then '' else '*****' end as 'Oddity'
from Users
inner join interesting on Id = interesting.UserId
left outer join awarded on Id = awarded.UserId
left outer join ranking on Id = ranking.UserId
order by Ranking
*/

select Id as [User Link],
       DisplayName,
       Ranking,
       QuestionCount,
       BadgeExpect
from Users
inner join ranking on Id = ranking.UserId
order by Ranking

fork query

edited oct 12 14 rolfl

created sep 24 14 Jon "not doe" Ericson