1

Please login or register to vote for this query.

(click on this box to dismiss)

For context: https://law.meta.stackexchange.com/q/1707/3209

Q&A for users and developers of the Cardano cryptocurrency ecosystem

-- based on https://data.stackexchange.com/law/query/160176/average-score-for-questions-and-answers-by-tag

WITH 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)/SQRT(COUNT(Questions.Id)) AS StdErrScore
         , CAST(COUNT(Questions.Id) AS DECIMAL) 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
), UntaggedAnswerStats 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)/SQRT(COUNT(Answers.Id)) AS StdErrScore
         , CAST(COUNT(Answers.Id) AS DECIMAL) 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
            AND Answers.Body NOT LIKE '%/tagged/%'
        GROUP BY Tags.Id, Tags.TagName
), OffJurisAnswerStats 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)/SQRT(COUNT(Answers.Id)) AS StdErrScore
         , CAST(COUNT(Answers.Id) AS DECIMAL) AS N
        FROM Tags
            INNER JOIN (
                SELECT Id
                     , ParentId
                     , Score
                     , SUBSTRING(Body, CHARINDEX('/tagged/', Body) + 8, CHARINDEX('"', Body, CHARINDEX('/tagged/', Body) + 8) - CHARINDEX('/tagged/', Body) - 8) AS AnswerTag
                    FROM Posts
                    WHERE 
                        PostTypeId = 2
                        AND Body LIKE '%/tagged/%'
            ) AS Answers ON Tags.TagName = Answers.AnswerTag
            INNER JOIN Posts AS Questions
                ON Questions.Id = Answers.ParentId
            WHERE
                CHARINDEX(Answers.AnswerTag, Questions.Tags) = 0
        GROUP BY Tags.Id, Tags.TagName
), OnJurisAnswerStats 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)/SQRT(COUNT(Answers.Id)) AS StdErrScore
         , CAST(COUNT(Answers.Id) AS DECIMAL) AS N
        FROM Tags
            INNER JOIN (
                SELECT Id
                     , ParentId
                     , Score
                     , SUBSTRING(Body, CHARINDEX('/tagged/', Body) + 8, CHARINDEX('"', Body, CHARINDEX('/tagged/', Body) + 8) - CHARINDEX('/tagged/', Body) - 8) AS AnswerTag
                    FROM Posts
                    WHERE 
                        PostTypeId = 2
                        AND Body LIKE '%/tagged/%'
            ) AS Answers ON Tags.TagName = Answers.AnswerTag
            INNER JOIN Posts AS Questions
                ON Questions.Id = Answers.ParentId
            WHERE
                CHARINDEX(Answers.AnswerTag, Questions.Tags) > 0
        GROUP BY Tags.Id, Tags.TagName
)
SELECT '<' + Tags.TagName + '>' AS [TagName]
     , QStats.AvgScore AS [Avg Q Score]
     , ROUND(QStats.StdErrScore, 2) AS [Q Std Err]
     , QStats.N AS [# Q]
     , UnTagAStats.AvgScore AS [Avg Untag A Score]
     , ROUND(UnTagAStats.StdErrScore, 2) AS [Untag A Std Err]
     , UnTagAStats.N AS [# Untag A]
     , OffJurAStats.AvgScore AS [Avg OffJur A Score]
     , ROUND(OffJurAStats.StdErrScore, 2) AS [OffJur A Std Err]
     , OffJurAStats.N AS [# OffJur A]
     , OnJurAStats.AvgScore AS [Avg OnJur A Score]
     , ROUND(OnJurAStats.StdErrScore, 2) AS [OnJur A Std Err]
     , OnJurAStats.N AS [# OnJur A]
    FROM
        Tags 
            LEFT OUTER JOIN TagQuestionStats AS QStats
                ON Tags.Id = QStats.Id
            LEFT OUTER JOIN UntaggedAnswerStats AS UnTagAStats
                ON Tags.Id = UnTagAStats.Id
            LEFT OUTER JOIN OffJurisAnswerStats AS OffJurAStats
                ON Tags.Id = OffJurAStats.Id
            LEFT OUTER JOIN OnJurisAnswerStats AS OnJurAStats
                ON Tags.Id = OnJurAStats.Id
    WHERE Tags.Id IN (4,50,12,130,1707,11,109,41,329,66,500,311,1331,178,159,210,232,452,225,360,312,665,542,534,234,342,714,256,259,592,86,80,300,282,241,1282,886,359,343,383,615,644,405,373,1864,454,323,628,914,1079,418,915,437,469,411,799,949,1397,284,1464,233)
    ORDER BY [# Q] DESC
           , [# Untag A] DESC
           , [Avg Q Score] DESC
           , [Avg Untag A Score] DESC         
           , [TagName];

fork query

edited feb 3 24

created feb 3 24 DPenner1