0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select distinct he.postid
       , he.id
  into #human_edits
  from posthistory he
  inner join posthistory ph on ph.postid = he.postid
  inner join posts p on ph.postid = p.id and p.answercount = 0 
  where ph.posthistorytypeid = 10
  and ph.comment = '1'
  and he.posthistorytypeid in (2,5,8)
  and he.id > ph.id
  and he.userdisplayname <> 'URL Rewriter Bot'


select postid as [Post Link] from #human_edits;
/*

;with md_rev as (
   select ph.postid
        , ph.id
   from posthistory ph
   inner join ( 
     select postid, max(id) latest
     from posthistory ph
     where posthistorytypeid in (2,5,8)
     group by postid ) md on md.postid = ph.postid and md.latest = ph.id
   where ph.text like '%<!-- End of automatically inserted text -->%'
), 
closed_posts as (
  select ph.id
       , ph.postid 
       , ph.text
  from posthistory ph
  inner join posts p on ph.postid = p.id and p.answercount = 0 
  where posthistorytypeid = 10
  and comment = '1'
), 
human_edits as (
  select postid
       , id
  from posthistory
  where posthistorytypeid in (2,5,8)
    and (userid >  0 or 
        (userdisplayname is not null  
         and userdisplayname <> 'URL Rewriter Bot') 
  )
),
closed_and_edited as (
  select cp.id
       , cp.postid 
  from closed_posts cp
  inner join md_rev md on md.postid = cp.postid
  where exists (
    select 1 
    from human_edits he 
    where he.postid = cp.postid 
    and he.id between cp.id and md.id
   )
  -- left outer join human_edits he 
   --- on he.postid = cp.postid 
   --    and he.id between cp.id and md.id
)

select id
     , postid as [Post Link]
from closed_and_edited
order by postid
*/


/*
select ph.id
     , ph.postid as [Post Link]
     , concat('site://q/',oid.value,'|', target.title) [Duplicate Link] 
from posthistory ph
-- inner join posts p on p.id = ph.postid
-- inner join md_rev md on md.postid = ph.postid
cross apply openjson( ph.text, '$.OriginalQuestionIds') oid
inner join posts target on target.id = oid.value
-- where posthistorytypeid = 10
-- and comment = '1'
 --- and target.answercount = 0 -- being redirected to a target without an answer is useless as well
and p.answercount = 0
and md.creationdate > p.closeddate -- edited after closure?
and exists () -- assume human
            ) 
order by ph.postid
*/

fork query

edited aug 6

created aug 6 rene