This query shows the number of upvotes, downvotes and D/U ratio for the answers to the most common tags
Q&A for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO
-- Enter Query Title -- Enter Query Description 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 JOIN votes v ON v.postid = pa.id WHERE pt.tagid = t.id AND v.votetypeid = 3 ) AS downvotes 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], downvotes AS [Downvotes], ROUND(100.0 * downvotes / NULLIF(upvotes, 0), 2) AS [D/U ratio] FROM q ORDER BY 4 DESC