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