H-index (based on duplicates) ranking per tag (accepted only)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select row_number() over (order by max(H.LinkScore) desc) as [#]
  , H.OwnerUserId as [User Link], max(H.LinkScore) as HIndex
  , min(B.Class) as TagBadge
from (
  select S.OwnerUserId, S.LinkScore
    , sum(S.NumAnswers)
      over (partition by S.OwnerUserId order by S.LinkScore desc rows unbounded preceding)
      as NumAnswersCumul
  from (
    select P.OwnerUserId
      , L.LinkScore
      , count(*) as NumAnswers
    from Posts P
    inner join (
      select PL.RelatedPostId, count(*) as LinkScore
      from PostLinks PL
      inner join Posts PS on PS.Id = PL.PostId -- removing deleted source questions
      inner join PostTags PT on PT.PostId = PL.RelatedPostId
      inner join Tags T on T.Id = PT.TagId
      where PL.LinkTypeId = 3
        and T.TagName = '##TagName?python##'
      group by PL.RelatedPostId
      ) L on L.RelatedPostId = P.ParentId
    inner join Posts PQ on PQ.Id = P.ParentId
    where P.PostTypeId = 2
      and L.LinkScore >= 1
      and P.OwnerUserId is not null
      and PQ.AcceptedAnswerId = P.Id
    group by P.OwnerUserId, L.LinkScore, PQ.Id /* grouping multiple answers to the same question*/
    ) S
  ) H
inner join Badges B on B.UserID = H.OwnerUserId
where H.NumAnswersCumul >= H.LinkScore
  and B.TagBased = 1
  and B.Name = '##TagName?python##'
group by H.OwnerUserId
order by HIndex desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results