1

Please login or register to vote for this query.

(click on this box to dismiss)

Modernized version of this query: https://data.stackexchange.com/stackoverflow/query/17950/how-many-questions-have-i-helped-to-close Basically, it breaks down everything by tag instead of user.

Q&A for people interested in the history and origins of science and mathematics

-- Based off of this query: https://data.stackexchange.com/stackoverflow/query/17950/how-many-questions-have-i-helped-to-close

DECLARE @closeReasons table
(
    Id int NOT NULL PRIMARY KEY,
    Description nvarchar(200) NOT NULL
)

INSERT INTO @closeReasons
    VALUES
    (1, 'Duplicate'),
    (2, 'Off topic'),
    (3, 'Subjective and argumentative'),
    (4, 'Not a real question'),
    (7, 'Too localized'),
    (20, 'Noise or pointless'),
    (101, 'Duplicate'),
    (102, 'Off topic'),
    (103, 'Unclear what youre asking'),
    (104, 'Too broad'),
    (105, 'primarily opinion-based'),
    (201, 'Off topic (channels)')
 

SELECT
    (
        CASE WHEN GROUPING(Description) = 1 THEN
            '(Total)'
        ELSE
            (CASE WHEN Description IS NULL THEN '(Unknown)' ELSE Description END)
        END
    ) AS 'Close Reason',
    SUM(Count) AS Count
    FROM
    (
        SELECT
            CAST(PostHistory.Comment AS int) AS CloseReasonId,
            1 AS Count            
            FROM PostHistory
            LEFT OUTER JOIN Posts ON Posts.Id = PostHistory.PostId
            WHERE
                (PostHistoryTypeId = 10) AND
                (Posts.Tags LIKE '%<##tag##>%')
    ) a
    LEFT OUTER JOIN @closeReasons cr ON cr.Id = a.CloseReasonId
    GROUP BY Description WITH ROLLUP
    ORDER BY
        GROUPING(Description),
        SUM(Count) DESC

fork query

created dec 5 19 Chipster