0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for community managers, administrators, and moderators

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

create index ix_posttype on #posts(posttypeid)
create index ix_parentid on #posts(parentid)
create index ix_acceptedid on #posts(AcceptedAnswerId)

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, 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

view current version

edited sep 22 19

created sep 18 19 rene