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

-- https://meta.stackexchange.com/q/226092

declare @LQTaskTypeId tinyint = 3

declare @UpMod tinyint = 2
declare @DownMod tinyint = 3

declare @RecommendDeletion tinyint = 9 
declare @Deletion tinyint = 4

; with scoreovertime as 
(
select rt.postid
     , rt.id
     , sum(case when votetypeid = @UpMod 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 = @LQTaskTypeId
and v.votetypeid in ( @UpMod, @DownMod)
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       -- only completed reviews
and ReviewTaskTypeId = @LQTaskTypeId            -- LQ
and (sot.postid is null or sot.score < 1)       -- not positive scored
group by rt.postid, rt.id
having sum(case when reviewtaskresulttypeid = @RecommendDeletion
                then 1 
                else 0 
                end) > ( -- https://meta.stackoverflow.com/q/342632
                         case when db_name() = 'StackOverflow' 
                               and max(rt.deletiondate) > '2017-01-30' 
                         then 3
                         else 5 
                         end) 
    or sum(case when reviewtaskresulttypeid = @Deletion
                then 1 
                else 0 
                end) > 2

view current version

edited feb 28

created feb 27 rene