1

Disclaimer

I don't know how to write SEDE queries, so I'm afraid I have to ask you to do it for me. I don't even know what is possible and what is not, so please forgive my ignorance.

The possibly impossible query

I would like a query to find all questions which were closed as duplicate, matching some important 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

I don't know it SEDE has data showing the score of a duplicate at the time of closure, so please bear with me if my request is pie-chasing-moonbeams-across-the-sky.

Alternatively

If this is not possible, please rather write a query to list all questions

  • longer than 300 words
  • answered
  • closed as duplicate
  • with a negative score

I hope this will not be too much of a pain. I actually do have a good reason to require this data, which I may disclose sometime soon, depending on what the data suggests. Thank you for your patience.

  • summons @rene – Glorfindel Jan 13 '18 at 16:41
  • For a single site or networkwide? – rene Jan 13 '18 at 17:02
  • @rene: For a single site. It can be run on different sites afterwards can't it, if it is written for a single site? – hat Jan 13 '18 at 17:21
  • yep, that is the default behavior – rene Jan 13 '18 at 17:22
7

Here is the impossible query:

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

I've annotated the query as much as possible to explain what is what.

Getting the score at the time of closing is a bit wobbly because the votes table doesn't have the time for votes that are casted (to preserve anonymity), best you can do is per date. The score at time of closing might be off and that can't be fixed within the public SEDE.

| improve this answer | |
  • I know it's bad to say thank you in comments, but: Thank you. I really thought I would just be told that I am asking the impossible. – hat Jan 13 '18 at 17:59
  • 4
    You are asking the impossible. But this is MSE where the magic happens ... – rene Jan 13 '18 at 18:00
  • Good thing I called in a wizard then, magic doesn't happen by itself! – hat Jan 13 '18 at 18:29

You must log in to answer this question.

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