2

I am attempting to perform some analysis on posts that have been deleted from review. I've built the query below, for SEDE, to help find which review tasks recommended deletion.

SELECT 
    t.id,
    'site://review/low-quality-posts/' + CAST(t.id as nvarchar) as [Review Link],
    t.ReviewTaskTypeId,
    tt.name,
    t.postid,
    r.reviewtaskresulttypeid,
    rt.name,
    r.creationdate
FROM
    ReviewTasks t,
    ReviewTaskTypes tt,
    ReviewTaskResults r,
    ReviewTaskResultTypes rt
WHERE
    t.reviewtasktypeid in (3)
    and t.reviewtasktypeid = tt.id
    and t.reviewtaskstateid = 2
    and r.reviewtaskid = t.id
    and r.reviewtaskresulttypeid in (4)
    and r.reviewtaskresulttypeid = rt.id
    and r.creationdate >= '2016-07-01'
ORDER BY
    t.postid

One of those results turns up this review that had 5 Recommend Deletion votes and 1 deletion vote. The post was deleted from review and then edited and undeleted.

Is there a way I can modify the SEDE query above to find the version that was deleted?

1

You might have some inconsistencies for edits that take place at the same date as the review is completed because review dates are only precise on the date where post edits are recorded in the posthistory on datetime.

Here is how the query works: For the creation and edit of the title, postbody or tags a record is inserted in the posthistory table with posthistorytypeid of 1, 2, 3, 4, 5 or 6. When those records are commited by the same revision they will have the same revisionguid. So to find the revision number before a certain date you have to count the unique rows with revisionguids for a given post. In the final step you can then build a link to /revisions/[postid[/[revsion] to get a directlink to that revision.

;with reviews as
(
SELECT 
    t.id,
    'site://review/low-quality-posts/' + CAST(t.id as nvarchar) as [Review Link],
    t.ReviewTaskTypeId,
    tt.name reviewname,
    t.postid,
    r.reviewtaskresulttypeid,
    rt.name,
    r.creationdate
FROM
    ReviewTasks t,
    ReviewTaskTypes tt,
    ReviewTaskResults r,
    ReviewTaskResultTypes rt
WHERE
    t.reviewtasktypeid in (3)
    and t.reviewtasktypeid = tt.id
    and t.reviewtaskstateid = 2
    and r.reviewtaskid = t.id
    and r.reviewtaskresulttypeid in (4)
    and r.reviewtaskresulttypeid = rt.id
    and r.creationdate >= '2016-07-01'
)

select 'site://revisions/' 
     + cast(r.postid as nvarchar)
     + '/' 
     + cast(rev as nvarchar) as revision
     , r.*
from (
  select id
       , postid
       , count(*) as rev
  from (
    select distinct r.id
         , r.postid
         , ph.revisionguid
    from reviews r
    inner join posthistory ph on ph.postid = r.postid 
    where posthistorytypeid in (1,2,3,4,5,6)
    and ph.creationdate < r.creationdate
    ) as allhist
  group by id, postid
) as gh
inner join reviews r on r.id = gh.id
order by postid

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .