0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for power users of web applications

WITH TagDownvoteMetrics AS (
    SELECT 
        Tags.Id AS TagId,
        Tags.TagName, 
        SUM(CASE WHEN Posts.Score < 0 THEN 1 ELSE 0 END) AS DownvotedPosts,
        COUNT(Posts.Id) AS TotalPosts
    FROM 
        Tags
        INNER JOIN PostTags ON Tags.Id = PostTags.TagId
        INNER JOIN Posts ON PostTags.PostId = Posts.Id
    WHERE 
        PostTypeId = 1  -- only questions
    GROUP BY 
        Tags.Id, Tags.TagName
    HAVING 
        COUNT(Posts.Id) > 1000  -- consider only tags with more than 1000 associated questions to ensure high activity
)

SELECT TOP 10
    TagName,
    DownvotedPosts AS NumberOfDownvotedPosts,
    TotalPosts AS TotalNumberOfPosts,
    CAST(DownvotedPosts AS FLOAT) / TotalPosts AS DownvoteRatio
FROM 
    TagDownvoteMetrics
ORDER BY 
    DownvoteRatio DESC; -- Highest downvote ratio first

fork query

created jun 28 anonymous