people reached total

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

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

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
 --  And OwnerUserId = ##UserId##
Union
Select ParentId, OwnerUserId
  From Posts
 Where PostTypeId = 2
   And Id in (select AcceptedAnswerId from Posts)
   and owneruserid is not null
   -- And OwnerUserId = ##UserId##
Union
Select ParentId, OwnerUserId
  From Posts
 Where PostTypeId = 2
   And Score > 5
   and owneruserid is not null
   -- And OwnerUserId = ##UserId##
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
   -- And a.OwnerUserId = ##UserId##
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
           -- And a.OwnerUserId = ##UserId##
            ) x
            Where AnswerRank <= 3) rp on rp.id = p.id
group by rp.owneruserid
order by sum(isnull(p.viewcount,0)) desc

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results