Required: One SEDE wizard (for a possibly impossible query)[forked]

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/305727/required-one-sede-wizard-for-a-possibly-impossible-query a query to find all questions which were closed as duplicate, matching these conditions: it must have been closed as duplicate while the score was not negative it must have been closed as duplicate while the score was below 3 it now has a negative score it is less than a year old

Startups Meta

Q&A for entrepreneurs faced with delivering a new product or service under conditions of significant uncertainty

select p.id as [Post Link]
from posts p
inner join posthistory ph on ph.postid = p.id
inner join votes v on v.postid = p.id
where p.score > 0  -- now negative score
and p.closeddate is not null -- now closed
and p.creationdate > dateadd(d,-366, getdate()) -- a year
and ph.creationdate = p.closeddate -- make sure we have one event
and ph.posthistorytypeid = 10 -- close
and comment = 101 -- duplicate
and v.votetypeid in (2,3) -- upmod / downmod
and v.creationdate <= p.closeddate -- votes before the post was closed
group by p.id
having sum(
       case 
       -- upvotes before the post was closed
       when v.votetypeid = 2 then 1  
       -- downvotes before the post was closed
       when v.votetypeid = 3 then -1 
       else 0 
       end) not between -3 and 0 -- lower then 3 and positive

Enter Parameters

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