H-index (based on answer scores) ranking

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

select row_number() over (order by max(H.Score) desc) as [#]
  , H.OwnerUserId as [User Link], max(H.Score) as HIndex
from (
  select S.OwnerUserId, S.Score
    , sum(S.NumAnswers)
      over (partition by S.OwnerUserId order by S.Score desc rows unbounded preceding)
      as NumAnswersCumul
  from (
    select P.OwnerUserId, P.Score, count(*) as NumAnswers
    from Posts P
    where P.PostTypeId = 2
        and P.Score >= 4
        and P.OwnerUserId is not null
    group by P.OwnerUserId, P.Score
    ) S
  ) H
where H.NumAnswersCumul >= H.Score and H.score = 4
group by H.OwnerUserId
order by [User Link] desc

Enter Parameters

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