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 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
         , 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.AliasId
            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.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
)
SELECT QStats.Id AS [Tags]
     , 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]
     , CAST(AStats.N AS FLOAT) / CAST(QStats.N AS FLOAT) 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;

view current version

edited jan 16 14

created jan 16 14 200_success