Answer rate over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 
DATEADD(d, (DAY(q.CreationDate) * -1) + 15, CAST(q.CreationDate AS DATE)) As [Date],
  AVG(
    CASE WHEN 
      DATEDIFF(hour, q.CreationDate, a.MC) <= 48
      THEN 1.0 
      ELSE 0.0 
    END
 ) As [AvgAnswered]
FROM PostsWithDeleted q
LEFT JOIN (
  SELECT MIN(CreationDate) mc, ParentId
  FROM PostsWithDeleted 
  WHERE PostTypeId = 2
  GROUP BY ParentId
) a ON a.ParentId = q.Id
WHERE q.PostTypeId = 1
GROUP BY DATEADD(d, (DAY(q.CreationDate) * -1) + 15, CAST(q.CreationDate AS DATE)) 
ORDER BY DATEADD(d, (DAY(q.CreationDate) * -1) + 15, CAST(q.CreationDate AS DATE))  ASC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results