Median time to first answer by month for a given tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Photography

Q&A for professional photographers, photo editors, and serious enthusiasts

/*
 * Treats unanswered questions as being answered after 9999999 minutes (~20 years)
 *   => This should not skew the median, unless <50% of questions are unanswered
 *   => If <50% have been answered, the median is "infinity", aka "9999999"
 * Ignores questions from before the site was created (usually due to migration)
 * Ignores closed or deleted questions
 * Ignores negatively-scored questions
 * Ignores deleted answers
 * Does not check for upvoted / accepted answers
 */

declare @FirstId int = (SELECT min(Id) FROM dbo.Posts WHERE PostTypeId = 1);
DECLARE @FirstQuestion datetime = (SELECT CreationDate FROM dbo.Posts WHERE Id = @FirstId);
DECLARE @LastQuestion datetime = (SELECT MAX(CreationDate) FROM dbo.Posts WHERE PostTypeId = 1);

SELECT TagName,
  ISNULL(DATEDIFF(second, q.CreationDate, MIN(a.CreationDate)), 9999999 * 60) TTA,
  q.CreationDate
INTO #AnswerTimes
FROM dbo.Posts q
     join PostTags on q.Id = PostId
     join Tags t on t.Id = TagId 
     left JOIN dbo.Posts a ON a.ParentId = q.Id AND a.OwnerUserId <> q.OwnerUserId
WHERE q.PostTypeId = 1 
  AND q.CreationDate >= @FirstQuestion 
  AND q.ClosedDate IS NULL
  AND q.Score >= 0
  and a.CreationDate > q.CreationDate
  and t.TagName in ('##tag##')
GROUP BY q.Id, q.CreationDate, TagName

SELECT
   MonthStart, round(MAX(Median), 0) minutes
FROM 
(
  SELECT
    dateadd(month, datediff(month, '20000101', X.CreationDate) , '20000101') AS MonthStart,  
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY TTA) OVER (PARTITION BY dateadd(month, datediff(month, '20000101', X.CreationDate) , '20000101')) / 60.0 Median
  FROM #AnswerTimes X
) RelevantQuestionsByWeek
GROUP BY MonthStart
ORDER BY MonthStart ASC;



--SELECT TagName tag,
--       round(MAX(Median), 0) minutes
--FROM (
--  SELECT X.TagName TagName,
--  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY TTA) OVER (PARTITION BY X.TagName) / 60.0 Median
--  FROM #AnswerTimes X
--  ) m
--group by TagName ,YEAR(X.CreationDate)
--order by TagName

Enter Parameters

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