6

When an answer is deleted from Low Quality Review by under-20K users, this is recorded as "Post Deleted from Review" in the revision history.

However, there is no record of this event in the Data Explorer. For example, this post was deleted from review, then undeleted and brought into shape (so far so good). But

I don't know if entering a pseudo-vote in the Votes table is really needed, but having a record of the deletion in Post History would make perfect sense. As is, it shows undeletion without prior deletion.

2

Since this question was asked it is now better possible to find those posts because we got the ReviewTasks and ReviewTasksResult at our disposal. There still might be some false positives so keep that mind.

The query finds the ReviewTasks for the LQP queue with ReviewTaskType =3. We then join with ReviewTaskResults so we can count the number of Delete and Recommend Deletion reviews. By joining with Votes we can determine if the score of the post at the date of the review completion was positive (as that prevents deletion from review).

Background on the logic found in Let's get rid of the 10K flag queue and Testing, testing... Let's test the low quality review queue for science! (thanks to Sonic for digging up those references)

declare @LQTaskTypeId tinyint = 3

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

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

declare @RecDelReviewsSO int = 4
declare @RecDelReviews int = 6

declare @DelReviews int = 3

declare @SOScience date = '2017-01-30' -- https://meta.stackoverflow.com/q/342632

; 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) > @SOScience 
                         then @RecDelReviewsSO
                         else @RecDelReviews 
                         end) 
    or sum(case when reviewtaskresulttypeid = @Deletion
                then 1 
                else 0 
                end) >= @DelReviews 

Keep in mind this will only show posts that were later undeleted because the ReviewTasks table is sanitized for deleted posts.

When run today you'll get this result:

table with posttitle, timeline and review

The first result is for example this post timeline.

Keep in mind that SEDE is only updated once a week on Sunday.
Give a big shoutout to Monica Cellio for her awesome tutorial
Hop into the SEDE Chatroom and say "Hi!".

4
  • The last line is > 3 for Stack Overflow only and > 5 for all other network sites. Is there a way to make this query agnostic for all sites including SO? – Sonic the Anonymous Hedgehog Feb 27 at 23:35
  • @SonictheCuriouserHedgehog was that always the case or was there some date where it got lowered on SO? – rene Feb 27 at 23:50
  • See meta.stackoverflow.com/q/342632/2157772. It was implemented as a temporary 30-day experiment beginning January 30, 2017, and was implemented permanently on March 3, 2017. – Sonic the Anonymous Hedgehog Feb 28 at 0:32
  • @SonictheCuriouserHedgehog okay, thanks for those links. Now changed the query according to that information. – rene Feb 28 at 7:51

You must log in to answer this question.