Average score for questions and answers, by tag

0

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?

Christianity

Q&A for committed Christians, experts in Christianity and those interested in learning more

WITH CanonicalTags AS (
    SELECT Id, TagName, TagName AS Alias FROM Tags
        WHERE NOT EXISTS (
            SELECT Id
                FROM TagSynonyms
                WHERE SourceTagName=TagName AND ApprovedByUserId IS NOT NULL
        )
        UNION
    SELECT Tags.Id, TagName, TagSynonyms.SourceTagName
        FROM Tags
            INNER JOIN TagSynonyms
                ON TargetTagName=TagName
        WHERE ApprovedByUserId IS NOT NULL
), TagQuestionStats AS (
    SELECT Tags.Id AS Id
         , 'site://questions/tagged/' + CAST(Tags.TagName AS NVARCHAR) + '|' + CAST(Tags.TagName AS NVARCHAR) AS [Tag]
         , AVG(Questions.Score) AS AvgScore
         , STDEV(Questions.Score) AS StdDevScore
         , COUNT(Questions.Id) AS N
        FROM CanonicalTags AS Tags
            INNER JOIN PostTags
                ON PostTags.TagId=Tags.Id
            INNER JOIN Posts AS Questions
                ON Questions.Id=PostTags.PostId
        GROUP BY Tags.Id, Tags.TagName
), TagAnswerStats AS (
    SELECT Tags.Id AS Id
         , AVG(Answers.Score) AS AvgScore
         , STDEV(Answers.Score) AS StdDevScore
         , COUNT(Answers.Id) AS N
        FROM CanonicalTags AS Tags
            INNER JOIN PostTags
                ON PostTags.TagId=Tags.Id
            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
)
SELECT QStats.[Tag] AS Tag
     , QStats.AvgScore AS [Avg Question Score]
     , QStats.StdDevScore AS [Question Score Std Dev]
     , QStats.N AS [# Questions]
     , AStats.AvgScore AS [Avg Answer Score]
     , AStats.StdDevScore AS [Answer Score Std Dev]
     , AStats.N AS [# Answers]
    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 sites:
loading Hold tight while we fetch your results