Is there a way to get the version of a post that was deleted from review in SEDE?

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/284505/is-there-a-way-to-get-the-version-of-a-post-that-was-deleted-from-review-in-sede

Stack Overflow

Q&A for professional and enthusiast programmers

;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)
     +'|rev ' + 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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results