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?

Economics

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 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, 4, 1;

Enter Parameters

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