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?

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;

view current version

edited jan 16 14

created jan 16 14 200_success