Average tag statistics with answers

1

Please login or register to vote for this query.

(click on this box to dismiss)

Views, Score and Answers per Tag (with answers)

Chess

Q&A for serious players and enthusiasts of chess

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

SELECT 
  TagName AS Tag,
  [Avg Q Views],
  [Avg Q Score],
  [Avg A Score],
  [Avg A Count],
  [% Accepted]
FROM
  (SELECT TOP(100)  -- top 100 most popular tags (tried 1000 but that timed out on SO)
      PostTags.TagId,
      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

LEFT JOIN 

  (SELECT
      PostTags.TagId,
      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

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