set showplan_all ON go create table #posts(id int , paren...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

set showplan_all ON
go
create table #posts(id int 
                   , parentid int
                   , score int
                   , viewcount int
                   , owneruserid int
                   , posttypeid tinyint
                   , AcceptedAnswerId int);


create clustered index ix_clus on #posts(Id) --, OwnerUserId) --, Score Desc)
-- create index ix_posttype on #posts(posttypeid)
-- create index ix_parentid on #posts(parentid)
-- create index ix_acceptedid on #posts(AcceptedAnswerId)
create index ix_2 on #posts(ParentId
                          -- , OwnerUserId
                          , Score Desc)

insert
into #posts
select id
     , parentid
     , score
     , isnull(viewcount,0)
     , owneruserid
     , posttypeid
     , acceptedAnswerId
from posts
where owneruserid is not null
and posttypeid in (1, 2)


select top 100 
       sum(p.viewcount) as reached
     , rp.owneruserid as [User Link]
from #posts p
inner join (
/*  Select Id, OwnerUserId 
  From #Posts 
  Where PostTypeId = 1 
  Union
  Select ParentId, OwnerUserId
    From #Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from #Posts where posttypeid = 1)
  Union
  Select ParentId, OwnerUserId
    From #Posts
   Where PostTypeId = 2
     And Score > 5
  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
  Union 
*/
  Select x.ParentId Id, 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
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
group by rp.owneruserid
order by sum(p.viewcount) desc

Enter Parameters

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