25

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows the number of upvotes, downvotes and D/U ratio for the answers to the most common tags

Q&A about the site for anime and manga fans

-- 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

fork query

created may 27 10 Quassnoi