0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/233763

Q&A for people looking to improve their interpersonal communication skills

; with scoreovertime as 
(
select rt.postid
     , rt.id
     , sum(case when votetypeid=2 then 1 else -1 end)  score
from reviewtasks rt
inner join votes v on v.postid = rt.postid
where completedbyreviewtaskid is not null 
and ReviewTaskTypeId = 3
and v.votetypeid in (2,3)
and v.creationdate <= rt.deletiondate
group by rt.postid, rt.id
)

select top 1000 
       rt.postid [Post Link]
     , concat('site://posts/',rt.postid,'/timeline|timeline') [Timeline Link]
     , concat('site://review/low-quality-posts/', rt.id,'|review') [Review Link]
from reviewtasks rt
inner join reviewtaskresults rtr 
  on rtr.reviewtaskid = rt.id
left outer join scoreovertime sot 
  on sot.postid = rt.postid and sot.id = rt.id
where completedbyreviewtaskid is not null 
and ReviewTaskTypeId = 3
and (sot.postid is null or sot.score < 1)
group by rt.postid, rt.id
having sum(case when reviewtaskresulttypeid = 9 
                then 1 
                else 0 
                end) > 5 -- rec del
    or sum(case when reviewtaskresulttypeid = 4 
                then 1 
                else 0 
                end) > 2 -- del

view current version

edited feb 27

created feb 27 rene