first-day closures who come back on another day

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mi Yodeya

Q&A for those who base their lives on Jewish law and tradition and anyone interested in learning more

select ucdfp.owneruserid as [User Link], count(p2.id) as futureposts
from (
  select udfp.owneruserid, udfp.dfp
  from (
    select p.owneruserid, min(p.creationdate) as dfp -- Datetime of First Post
    from posts p
    where p.posttypeid < 3 -- Q/A
    group by p.owneruserid
    ) udfp
  left outer join posts p on
        p.owneruserid = udfp.owneruserid
    and p.posttypeid = 1 -- Q
  left outer join posthistory ph on
        ph.posthistorytypeid = 10 -- closure
    and ph.postid = p.id
    and datediff(hh, udfp.dfp, ph.creationdate) < 24
  group by udfp.owneruserid, udfp.dfp
  having count(ph.id) > 0
  ) ucdfp
left outer join posts p2 on
      p2.owneruserid = ucdfp.owneruserid
  and datediff(hh, ucdfp.dfp, p2.creationdate) >= 24
  and p2.posttypeid < 3 -- Q/A
group by ucdfp.owneruserid
having count(p2.id) > 0

Enter Parameters

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