Most and least rewarding tags to answer (among the tags with 1000+ questions)

2

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 answer to the most common tags

Stack Overflow

Q&A for programmers

-- Most and least rewarding tags to answer (among the tags with 1000+ questions)
-- This query shows the number of upvotes, answers, and average upvotes per 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
                ) 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
                ) 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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached