5

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 about the site for expert genealogists and people interested in genealogy or family history

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

fork query

edited jan 26 14

created jan 16 14 200_success