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 , Tags.TagName AS TagName , AVG(CAST(Questions.Score AS FLOAT)) 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 , Tags.TagName AS TagName , AVG(CAST(Answers.Score AS FLOAT)) 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, Tags.TagName ) SELECT '<' + QStats.TagName + '>' AS [TagName] , ROUND(QStats.AvgScore, 2) AS [Avg Qst Score] , ROUND(QStats.StdDevScore, 2) AS [Qst Score Std Dev] , QStats.N AS [# Qst] , ROUND(AStats.AvgScore, 2) AS [Avg Ans Score] , ROUND(AStats.StdDevScore, 2) AS [Ans Score Std Dev] , AStats.N AS [# Ans] , CAST(AStats.N AS FLOAT) / CAST(QStats.N AS FLOAT) 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;