For all canonical tags, what is the average question score and the average answer score?
Q&A for those who study, teach, research and apply economics and econometrics
WITH CanonicalTags AS ( SELECT Id, TagName, TagName AS Alias FROM Tags WHERE NOT EXISTS ( SELECT Id FROM TagSynonyms WHERE SourceTagName=TagName AND ApprovedByUserId IS NOT NULL ) UNION SELECT Tags.Id, TagName, TagSynonyms.SourceTagName FROM Tags INNER JOIN TagSynonyms ON TargetTagName=TagName WHERE ApprovedByUserId IS NOT NULL ), 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 [Avg Question Score] , QStats.StdDevScore AS [Question Score Std Dev] , QStats.N AS [# Questions] , AStats.AvgScore AS [Avg 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;