21

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the breakdown of the close reasons you've used in successfully closed questions.

Q&A for pro webmasters

-- How many questions have I helped to close?
-- Shows the breakdown of the close reasons you've used in successfully closed questions.

DECLARE @userId int = ##UserId##
DECLARE @searchUserId1 nvarchar(25) = '%"Id":' + CAST(@userId AS nvarchar(MAX)) + '}'
DECLARE @searchUserId2 nvarchar(25) = '%"Id":' + CAST(@userId AS nvarchar(MAX)) + ',%'

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

INSERT INTO @closeReasons
    VALUES
    (1, 'Exact duplicate'),
    (2, 'Off topic'),
    (3, 'Subjective and argumentative'),
    (4, 'Not a real question'),
    (7, 'Too localized'),
    (20, 'Noise or pointless')

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
            WHERE
                (PostHistoryTypeId = 10) AND
                ((Text LIKE @searchUserId1) OR (Text LIKE @searchUserId2))
    ) 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 jan 9 11 Jon Seigel