Average score for questions and answers, by tag

5

Please login or register to vote for this query.

(click on this box to dismiss)

For all canonical tags, what is the average question score and the average answer score?

Sports

Q&A for participants in team and individual sport activities

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
         , AVG(CAST(Questions.Score AS DECIMAL)) 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 DECIMAL)) 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]
     , ROUND(CAST(AStats.N AS DECIMAL) / CAST(QStats.N AS DECIMAL), 2) 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 DESC, 4 DESC, 1;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results