5

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/253579/include-the-people-reached-data-in-sede

Q&A about the site for scholars and enthusiasts of literature

select top 100 
       sum(isnull(p.viewcount,0)) as reached
     , rp.owneruserid as [User Link]
from posts p
inner join (
  Select Id, OwnerUserId 
  From Posts 
  Where PostTypeId = 1 
  and owneruserid is not null
  Union
  Select ParentId, OwnerUserId
    From Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from Posts)
     and owneruserid is not null
  Union
  Select ParentId, OwnerUserId
    From Posts
   Where PostTypeId = 2
     And Score > 5
     and owneruserid is not null
  Union
  Select a.ParentId, a.OwnerUserId
    From Posts a
         Join Posts q On a.ParentId = q.Id
   Where a.PostTypeId = 2
     And a.Score > 0.20 * (select sum(Score) from Posts where ParentId=q.Id)
     And a.Score > 0
     and a.owneruserid is not null
  Union
  Select x.ParentId, x.OwnerUserId 
    From (Select a.ParentId , a.OwnerUserId, Rank() Over(Partition By a.ParentId, a.OwnerUserId Order By ta.Score Desc) AnswerRank
            From Posts a
                 Join Posts ta On ta.ParentId = a.ParentId
           Where a.PostTypeId = 2
             And a.Score > 0
             and a.owneruserid is not null
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
group by rp.owneruserid
order by sum(isnull(p.viewcount,0)) desc

fork query

edited apr 16 15

created apr 16 15 rene