5

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/264569/how-can-i-sort-my-posts-by-impact-people-reached

Q&A for those who study, teach, research and apply economics and econometrics

declare @userid int = ##userid:int##

select p.id as [Post Link]
     , p.viewcount
from posts p
inner join (
  Select Id 
  From Posts 
  Where PostTypeId = 1 
  and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from Posts)
     and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Score > 5
     and owneruserid = @userid
  Union
  Select a.ParentId
    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 = @userid
  Union
  Select x.ParentId
    From (Select a.ParentId, Rank() Over(Partition By a.ParentId 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 = @userid
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
order by viewcount desc

fork query

edited sep 1 15

created sep 1 15 rene