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, Id AS AliasId, TagName AS Alias FROM Tags WHERE NOT EXISTS ( SELECT Id FROM TagSynonyms WHERE SourceTagName=TagName AND ApprovedByUserId IS NOT NULL ) UNION SELECT Tags.Id, Tags.TagName, Alias.Id AS AliasId, Alias.TagName FROM Tags INNER JOIN TagSynonyms ON TargetTagName=TagName INNER JOIN Tags AS Alias ON Alias.TagName=SourceTagName 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 CanonicalTags AS Tags INNER JOIN PostTags ON PostTags.TagId=Tags.AliasId 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 CanonicalTags AS Tags INNER JOIN PostTags ON PostTags.TagId=Tags.AliasId 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 Qst Score] , QStats.StdDevScore AS [Qst Score Std Dev] , QStats.N AS [# Qst] , AStats.AvgScore AS [Avg Ans Score] , AStats.StdDevScore AS [Ans Score Std Dev] , AStats.N AS [# Ans] , AStats.N / QStats.N AS [Avg # Ans/Qst] FROM TagQuestionStats AS QStats LEFT OUTER JOIN TagAnswerStats AS AStats ON AStats.Id=QStats.Id ORDER BY 5 DESC, 2 DESC, 7, 4, 1;