0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

WITH    q AS
        (SELECT  t.*,
                (
                SELECT  COUNT(*) AS cnt
                FROM    posttags pt
                JOIN    posts pp
                ON      pp.id = pt.postid
                JOIN    posts pa
                ON      pa.parentid = pp.id
                WHERE   pt.tagid = t.id
                         AND pa.score >= 25
                        AND pa.CommunityOwnedDate IS NULL
                ) AS GoodAnswers,
                (
                SELECT  COUNT(*) AS cnt
                FROM    posttags pt
                JOIN    posts pp
                ON      pp.id = pt.postid
                JOIN    posts pa
                ON      pa.parentid = pp.id
                WHERE   pt.tagid = t.id
                         AND pa.score = 0
                        AND pa.CommunityOwnedDate IS NULL
                ) AS ZeroPointAnswers,
                (
                SELECT  COUNT(*) AS cnt
                FROM    posttags pt
                JOIN    posts pp
                ON      pp.id = pt.postid
                JOIN    posts pa
                ON      pa.parentid = pp.id
                WHERE   pt.tagid = t.id
                        AND pa.CommunityOwnedDate IS NULL
                ) AS answers
                FROM    Tags t
                 CROSS APPLY
                (
                SELECT  COUNT(*) AS cnt
                FROM    PostTags pt
                WHERE   pt.tagid = t.id
                HAVING  COUNT(*) >= 1000
                ) pt
                )
                
SELECT  
rank() over (ORDER BY ROUND(CAST(GoodAnswers as DECIMAL (10,5)) / NULLIF(answers, 0), 6) DESC ) GoodAnswerRank,
                rank() over (ORDER BY ROUND(CAST(ZeroPointAnswers as DECIMAL) / NULLIF(answers, 0), 6) DESC ) ZeroAnswerRank,
                tagname AS [Tags], 
                GoodAnswers AS [GoodAnswers], 
                answers AS [Answers], 
                ZeroPointAnswers as [ZeroPointAnswers],
                ROUND(CAST(GoodAnswers as DECIMAL) / NULLIF(answers, 0), 6) AS [GoodAnswers Frequency],
                ROUND(CAST(ZeroPointAnswers as DECIMAL) / NULLIF(answers, 0), 6) AS [ZeroPointAnswers Frequency],
                rank() over (ORDER BY COUNT DESC) TagSizeRank
FROM q                
ORDER BY
        1

fork query

edited sep 7 12

created sep 7 12 conradfrix