Discoverability metrics per tag -- average time to first answer

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

-- MinScore: Minimum answer score
-- MaxDaysToFirstAnswer: Maximum number of days between question creation and first answer creation

WITH QuestionDiscoverabilityMetrics AS (
  SELECT Questions.Id,
    DATEDIFF(second, Questions.CreationDate, FirstAnswerDates.FirstAnswerDate) AS SecondsToFirst
  FROM Posts AS Questions
  INNER JOIN (
    SELECT ParentId, MIN(Posts.CreationDate) AS FirstAnswerDate
    FROM Posts
    WHERE PostTypeId = 2 AND Score >= ##MinScore:int?2##
    GROUP BY ParentId
  ) AS FirstAnswerDates ON Questions.Id = FirstAnswerDates.ParentId
  WHERE Questions.PostTypeId = 1
    AND DATEDIFF(second, Questions.CreationDate, FirstAnswerDates.FirstAnswerDate) < 60*60*24 * ##MaxDaysToFirstAnswer:int?30##
),
ExcelPosts AS (
  SELECT PostId
  FROM PostTags
  WHERE TagId = (SELECT Id FROM Tags WHERE TagName = 'excel')
),
VbaPosts AS (
  SELECT PostId
  FROM PostTags
  WHERE TagId = (SELECT Id FROM Tags WHERE TagName = 'vba')
),
ExcelVbaPosts AS (
  SELECT PostId
  FROM PostTags
  WHERE TagId = (SELECT Id FROM Tags WHERE TagName = 'excel-vba')
)

SELECT 'excel-vba' AS Description,
  COUNT(*) AS Count,
  AVG(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS AvgHoursToFirst,
  STDEV(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS StdevHoursToFirst
FROM QuestionDiscoverabilityMetrics
WHERE Id IN (SELECT PostId FROM ExcelVbaPosts)

UNION ALL SELECT 'excel + vba' AS Description,
  COUNT(*) AS Count,
  AVG(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS AvgHoursToFirst,
  STDEV(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS StdevHoursToFirst
FROM QuestionDiscoverabilityMetrics
WHERE Id IN (SELECT PostId FROM ExcelPosts)
  AND Id IN (SELECT PostId FROM VbaPosts)

UNION ALL SELECT 'excel-vba, without excel or vba' AS Description,
  COUNT(*) AS Count,
  AVG(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS AvgHoursToFirst,
  STDEV(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS StdevHoursToFirst
FROM QuestionDiscoverabilityMetrics
WHERE NOT Id IN (SELECT PostId FROM ExcelPosts)
  AND NOT Id IN (SELECT PostId FROM VbaPosts)
  AND Id IN (SELECT PostId FROM ExcelVbaPosts)

UNION ALL SELECT 'excel + vba, without excel-vba' AS Description,
  COUNT(*) AS Count,
  AVG(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS AvgHoursToFirst,
  STDEV(CAST(SecondsToFirst AS FLOAT))/60.0/60.0 AS StdevHoursToFirst
FROM QuestionDiscoverabilityMetrics
WHERE Id IN (SELECT PostId FROM ExcelPosts)
  AND Id IN (SELECT PostId FROM VbaPosts)
  AND NOT Id IN (SELECT PostId FROM ExcelVbaPosts)

Enter Parameters

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