0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with two_answers as (
  select min(a.Id) first, max(a.Id) last
  from Posts q
       join Posts a on q.Id = a.ParentId
  where q.AnswerCount = 2
  group by q.Id
  having getdate() - max(a.CreationDate) > 30
)

select min(datediff(hour, f.CreationDate, l.CreationDate)),
       avg(cast(f.Score as float)) first, avg(cast(l.Score as float)) last,
       avg(cast(isnull(f.Score, 0) as float)
            - cast(isnull(l.Score, 0) as float)) delta
from two_answers
     join Posts f on f.Id = first
     join Posts l on l.Id = last
where isnull(f.Score, 0) >= 0
      and isnull(l.Score, 0) >= 0
      and f.CreationDate < l.CreationDate
      and datediff(month, f.CreationDate, l.CreationDate) < 1
      group by datediff(hour, f.CreationDate, l.CreationDate)
      order by datediff(hour, f.CreationDate, l.CreationDate)

fork query

edited mar 11 14

created mar 11 14 Jon "not doe" Ericson