For all canonical tags, what is the average question score and the average answer score?
Q&A for committed Christians, experts in Christianity and those interested in learning more
WITH TagQuestionStats AS ( SELECT Tags.Id AS Id , 'site://questions/tagged/' + CAST(Tags.TagName AS NVARCHAR) + '|' + CAST(Tags.TagName AS NVARCHAR) AS [Tag] , AVG(Questions.Score) AS AvgScore , STDEV(Questions.Score) AS StdDevScore , COUNT(Questions.Id) AS N FROM Tags INNER JOIN PostTags ON PostTags.TagId=Tags.Id INNER JOIN Posts AS Questions ON Questions.Id=PostTags.PostId GROUP BY Tags.Id, Tags.TagName ), TagAnswerStats AS ( SELECT Tags.Id AS Id , AVG(Answers.Score) AS AvgScore , STDEV(Answers.Score) AS StdDevScore , COUNT(Answers.Id) AS N FROM Tags INNER JOIN PostTags ON PostTags.TagId=Tags.Id INNER JOIN Posts AS Questions ON Questions.Id=PostTags.PostId INNER JOIN Posts AS Answers ON Answers.ParentId=Questions.Id WHERE Answers.PostTypeId=2 GROUP BY Tags.Id ) SELECT QStats.[Tag] AS Tag , QStats.AvgScore AS [Average Question Score] , QStats.StdDevScore AS [Question Score Std Dev] , QStats.N AS [# Questions] , AStats.AvgScore AS [Average Answer Score] , AStats.StdDevScore AS [Answer Score Std Dev] , AStats.N AS [# Answers] FROM TagQuestionStats AS QStats LEFT OUTER JOIN TagAnswerStats AS AStats ON AStats.Id=QStats.Id ORDER BY 5 DESC, 2 DESC, 7, 4, 1;