my impact2

0

Please login or register to vote for this query.

(click on this box to dismiss)

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

Stack Overflow

Q&A for professional and enthusiast programmers

declare @userid int = 1318479

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

Enter Parameters

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