3

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 Drupal developers and administrators

Declare @MinQuestioncount int 
SET @MinQuestioncount = ##MinQuestionCount:int##;

-- 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    votes v
                ON      v.postid = pp.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    votes v
                ON      v.postid = pp.id
                WHERE   pt.tagid = t.id
                        AND v.votetypeid = 3
                ) AS downvotes,
                pt.cnt
        FROM    Tags t
        CROSS APPLY
                (
                SELECT  COUNT(*) AS cnt
                FROM    PostTags pt
                WHERE   pt.tagid = t.id
                HAVING  COUNT(*) >= @MinQuestioncount
                ) pt
        )
        SELECT ROW_NUMBER() OVER (ORDER BY ROUND(100.0 * downvotes / (NULLIF(downvotes, 0) + NULLIF(upvotes, 0)), 2) DESC) Rank,  tagname AS [Tags], cnt as TagSize, upvotes AS [Upvotes], downvotes AS [Downvotes], ROUND(100.0 * downvotes / (NULLIF(downvotes, 0) + NULLIF(upvotes, 0)), 2) AS [D/U ratio]
FROM    q
ORDER BY
        1 asc

fork query

edited sep 4 13 conradfrix

created may 27 10 Quassnoi