Average tag statistics with answers


Please login or register to vote for this query.

(click on this box to dismiss)

Views, Score and Answers per Tag (with answers)


Q&A for community managers, administrators, and moderators

-- Tag statistics
-- Views, Score and Answers per Tag

  TagName AS Tag,
  [Avg Q Views],
  [Avg Q Score],
  [Avg A Score],
  [Avg A Count],
  [% Accepted]
  (SELECT TOP(100)  -- top 100 most popular tags (tried 1000 but that timed out on SO)
      Count(*) AS [Q Count], 
      Sum(Cast(Posts.ViewCount as bigint)) / Count(*) AS [Avg Q Views],
      str(Sum(Posts.Score) / Cast(Count(*) AS Float),6,2) AS [Avg Q Score],
      str(Sum(Posts.AnswerCount) / Cast(Count(*) AS Float),6,2) AS [Avg A Count],
      str(100 * Sum(CASE WHEN AcceptedAnswerId IS NULL THEN 0 ELSE 1 END) / Cast(Count(*) AS Float),6,2) AS [% Accepted]
    FROM Posts 
    JOIN PostTags ON Posts.Id = PostTags.PostId
    WHERE Posts.PostTypeId = 1 
    GROUP BY PostTags.TagId
    ORDER BY [Q Count] DESC) qStats


      str(Sum(a.Score) / Cast(Count(*) AS Float),6,2) AS [Avg A Score]
    FROM Posts a
    JOIN Posts q ON a.ParentId = q.Id
    JOIN PostTags ON PostTags.PostId = q.Id
    WHERE a.PostTypeId = 2 
    AND   q.PostTypeId = 1
    GROUP BY PostTags.TagId) aStats
ON qStats.TagId = aStats.TagId
LEFT JOIN Tags ON Tags.Id = aStats.TagId

ORDER BY [Avg Q Views] DESC;

Enter Parameters

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