0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

select distinct p.id as [Post Link]
from posts p
inner join posthistory ph on ph.postid = p.id
inner join postswithdeleted pwd on pwd.parentid = p.id
where posthistorytypeid = 19 -- protected
and not exists 
  (select 1 
   from posthistory phun 
   where phun.postid = p.id
   and phun.posthistorytypeid = 20 -- unprotected
   and phun.creationdate > ph.creationdate)
and p.posttypeid = 1 -- Question
and pwd.deletiondate is not null  
and pwd.posttypeid = 2 -- Answer

select distinct p.id as [Post Link]
from posts p
inner join posthistory ph on ph.postid = p.id
inner join postswithdeleted pwd on pwd.parentid = p.id
where posthistorytypeid = 19 -- protected
--and not exists 
--  (select 1 
--   from posthistory phun 
--   where phun.postid = p.id
--   and phun.posthistorytypeid = 20 -- unprotected
--   and phun.creationdate > ph.creationdate
--   and phun.creationdate < '2020-01-01')
and p.posttypeid = 1 -- Question
and pwd.deletiondate is not null  
and pwd.posttypeid = 2 -- Answer
and pwd.score < 0 -- assume a spam flag was cast on a fresh answer

fork query

edited jul 11 20

created jul 11 20 rene