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 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 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 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 [Average Question Score]
     , QStats.StdDevScore AS [Question Score Std Dev]
     , QStats.N AS [# Questions]
     , AStats.AvgScore AS [Average 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