0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

WITH Answers AS (
  SELECT Id, ParentId, CreationDate
    FROM Posts
    WHERE PostTypeId = 2 -- Answers
    AND Score > 0 -- positively voted
),
Questions AS (
  SELECT  Id, CreationDate, AcceptedAnswerId
    FROM Posts
    WHERE PostTypeId = 1 -- Questions
    AND DATEDIFF(DD, CreationDate, CURRENT_TIMESTAMP) BETWEEN 100 AND 400  -- some meaningful range
    AND DeletionDate is NULL
    AND ClosedDate is NULL
)

SELECT TIME_TO_ANSWER AS [Days until an Answer], COUNT(TIME_TO_ANSWER) AS [Occurency]
  FROM (
    -- question creation dates and time until first answer
    SELECT Questions.CreationDate, CASE WHEN MIN(Answers.CreationDate) IS NOT NULL THEN DATEDIFF(DD, Questions.CreationDate, MIN(Answers.CreationDate)) ELSE 100 END AS [TIME_TO_ANSWER]
      FROM Questions
      LEFT JOIN Answers 
      ON Questions.AcceptedAnswerId = Answers.Id
      GROUP BY Questions.CreationDate
  ) h
  WHERE TIME_TO_ANSWER BETWEEN 0 AND 100 -- some meaningful range

  GROUP BY TIME_TO_ANSWER
  ORDER BY TIME_TO_ANSWER
;

view current version

edited nov 13 19

created nov 13 19 jon.doe32184