Average score for questions and answers, by tag

4

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?

Cognitive Sciences Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

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];

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results