0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

/*
 * 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
   WeekStart, round(MAX(Median), 0) minutes
FROM 
(
  SELECT
    dateadd(week, datediff(week, '20000102', X.CreationDate) , '20000102') AS WeekStart,  
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY TTA) OVER (PARTITION BY dateadd(week, datediff(week, '20000102', X.CreationDate) , '20000102')) / 60.0 Median
  FROM #AnswerTimes X
) RelevantQuestionsByWeek
GROUP BY WeekStart
ORDER BY WeekStart 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

fork query

edited jan 18 19 anonymous

created sep 12 16 tristansokol