What is the median impact score on Stack Overflow?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackoverflow.com/questions/387408

Stack Overflow

Q&A for professional and enthusiast programmers

Select Id
     , ParentId
     , OwnerUserId
     , Score
     , PostTypeId
     , cast(isnull(viewcount,0) as bigint) ViewCount
Into #Posts
From Posts
Where owneruserid is not null

-- create index ix_id on #Posts(id)
-- create index ix_PosttypeId on #Posts(Posttypeid)

Select Id
     , ParentId
     , OwnerUserId
     , Score
     , PostTypeId
     , ViewCount
Into #PostsQ
From #Posts
Where PostTypeid = 1

Select Id
     , ParentId
     , OwnerUserId
     , Score
     , PostTypeId
     , ViewCount
Into #PostsA
From #Posts
Where PostTypeid = 2
and score > 0

-- create index ix_score on #PostsA(score desc)

create index ix_idq on #PostsQ(id)
-- create index ix_Parentidq on #PostsQ(parentid, OwnerUserid)
-- create index ix_ida on #Postsa(id)
create index ix_Parentida on #Postsa(parentid, OwnerUserid, score desc)


select top 1 -- avg(sum_of_viewcount)
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sum_of_viewcount)   
                            OVER () [Median of People reached]
from (
select sum(viewcount) Sum_of_viewcount --, p.owneruserid as reached
from #posts p
inner join (
  Select Id, OwnerUserId 
  From #PostsQ 
  Union
  Select ParentId, OwnerUserId
    From #Posts p
    inner join votes v on v.postid = p.iD
   Where VoteTypeId = 1  --  Id in (select AcceptedAnswerId from Posts)
  Union
  Select ParentId, OwnerUserId
    From #PostsA
   Where Score > 5
  Union
  Select a.ParentId, a.OwnerUserId
    From #PostsA a
         Join #PostsQ q On a.ParentId = q.Id
   Where a.Score > 0.20 * (select sum(Score) from #PostsA where ParentId=q.Id)
    --  And a.Score > 0
  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 #PostsA a
                 Join #PostsA ta On ta.ParentId = a.ParentId
           -- Where a.Score > 0
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.iD
group by p.owneruserid
) data

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results