Off-topic reason by tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Even better version of this: https://data.stackexchange.com/stackoverflow/query/1163060/breakdown-of-close-reasons-for-a-tag, but allows you to see what the common off-topic reasons are.

Physics

Q&A for active researchers, academics and students of physics

SELECT
    (
        CASE WHEN GROUPING(MarkdownMini) = 1 THEN
            '(Total)'
        ELSE
            (CASE WHEN MarkdownMini IS NULL THEN '(Unknown)' ELSE MarkdownMini END)
        END
    ) AS 'Close Reason',
    SUM(Count) AS Count
-- DEBUG
--    MarkdownMini, Tags, Count, FlagTypeId, CloseReasonId, CloseReasonTypeId
    FROM
    (
        SELECT
            CAST(PendingFlags.CloseAsOffTopicReasonTypeId AS int) AS CloseReasonId,
-- DEBUG
--            Posts.Tags as Tags,
--            FlagTypeId,
--            CloseReasonTypeId,
            1 AS Count            
            FROM PendingFlags
            LEFT OUTER JOIN Posts ON Posts.Id = PendingFlags.PostId
            WHERE
                (FlagTypeId = 14) AND
                (CloseReasonTypeId = 2 OR 
                    CloseReasonTypeId = 102 OR
                    CloseReasonTypeId = 201) AND
                (Posts.Tags LIKE '%<##tag##>%')
    ) a
    LEFT OUTER JOIN CloseAsOffTopicReasonTypes cr ON cr.Id = a.CloseReasonId
    GROUP BY MarkdownMini WITH ROLLUP
    ORDER BY
        GROUPING(MarkdownMini),
        SUM(Count) DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results