1

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows the number of upvotes, answers, and average upvotes per non-wiki answer to the most common tags

Q&A for enthusiasts and power users of the Android operating system

-- Most and least rewarding tags to answer (non-wiki, among the tags with 1000+ questions)
-- This query shows the number of upvotes, answers, and average upvotes per non-wiki answer to the most common tags

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
                JOIN    votes v
                ON      v.postid = pa.id
                WHERE   pt.tagid = t.id
                        AND v.votetypeid = 2
                        AND pa.CommunityOwnedDate IS NULL
                ) AS upvotes,
                (
                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  tagname AS [Tags], upvotes AS [Upvotes], answers AS [Answers], ROUND(CAST(upvotes as DECIMAL) / NULLIF(answers, 0), 2) AS [Avg Upvotes / Answer]
FROM    q
ORDER BY
        4 DESC

fork query

created may 27 10 Quassnoi