For all canonical tags, what is the average question score and the average answer score?
Q&A for Drupal developers and administrators
WITH CanonicalTags AS ( SELECT Master.Id AS Id , Master.TagName AS TagName , Synonym.Id AS AliasId , Synonym.TagName AS AliasName FROM Tags AS Synonym LEFT OUTER JOIN TagSynonyms ON SourceTagName = Synonym.TagName AND ApprovedByUserId IS NOT NULL LEFT OUTER JOIN Tags AS Master ON Master.TagName = COALESCE(TargetTagName, Synonym.TagName) ), TagQuestionStats AS ( SELECT Tags.Id AS Id , Tags.TagName AS TagName , CAST(AVG(CAST(Questions.Score AS DECIMAL)) AS DECIMAL(8,2)) AS AvgScore , CAST(STDEV(Questions.Score) AS DECIMAL(8,2)) AS StdDevScore , CAST(COUNT(Questions.Id) AS DECIMAL) 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 , CAST(AVG(CAST(Answers.Score AS DECIMAL)) AS DECIMAL(8,2)) AS AvgScore , CAST(STDEV(Answers.Score) AS DECIMAL(8,2)) AS StdDevScore , CAST(COUNT(Answers.Id) AS DECIMAL) 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] , 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] , ROUND(AStats.N / QStats.N, 2) AS [Avg # Ans/Qst] FROM TagQuestionStats AS QStats LEFT OUTER JOIN TagAnswerStats AS AStats ON AStats.Id = QStats.Id ORDER BY [Avg Ans Score] DESC , [Avg Qst Score] DESC , [# Ans] DESC , [# Qst] DESC , [TagName];