Highest Ratios People Reached/Questions Answered

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

SELECT TOP ##NumberOfResults## rp.OwnerUserId as [User Link], sum(isnull(p.viewcount,0))/NULLIF((SELECT Count(A.id) AS Answers FROM Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id
WHERE A.ParentID Is Not Null AND U.Id = rp.OwnerUserId),0) as Ratio, sum(isnull(p.viewcount,0)) AS PeopleReached, (SELECT Count(A.id) AS Answers FROM Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id
WHERE A.ParentID Is Not Null AND U.Id = rp.OwnerUserId) as Answers 
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

WHERE (SELECT Count(A.id) AS Answers FROM Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id
WHERE A.ParentID Is Not Null AND U.Id = rp.OwnerUserId) >= ##MinimumOfAnswers##

group by rp.owneruserid
/*FROM Posts INNER JOIN Users ON Posts.OwnerUserID=Users.Id 
GROUP BY Users.Id*/ 
ORDER BY sum(isnull(p.viewcount,0))/NULLIF((SELECT Count(A.id) AS Answers FROM Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id
WHERE A.ParentID Is Not Null AND U.Id = rp.OwnerUserId),0) DESC

Enter Parameters

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