Question closure classification, by date - absolute numbers

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query tracks the percentage of questions posted over the last n days that fall into each closure reason category. Results are best viewed as a graph. Since closed posts are deleted after a certain time, results are likely to be misleading if you look back too many days.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Last AS (
    SELECT MAX(CreationDate) AS CreationDate FROM Posts
), Questions AS (
    SELECT Posts.Id
         , COALESCE(Closure.Comment, 0) AS Comment
         , CAST(Posts.CreationDate AS DATE) AS QuestionDate
        FROM Last, Posts
            LEFT OUTER JOIN PostHistory AS Closure
                ON Closure.PostId = Posts.Id
                AND Closure.PostHistoryTypeId = 10
            LEFT OUTER JOIN PostHistory AS Reopening
                ON Reopening.PostId = Posts.Id
                AND Reopening.PostHistoryTypeId = 11
        WHERE
            Posts.PostTypeId = 1 -- Question
            AND Reopening.Id IS NULL
            AND DATEDIFF(day, Posts.CreationDate, Last.CreationDate) <= ##Days?120##
), ClosureCommentByQuestionDate AS (
    SELECT QuestionDate
         , CASE                            -- Map old->new close reasons:
               WHEN Comment =  1 THEN 101  -- Exact duplicate
               WHEN Comment =  2 THEN 102  -- Off-topic
               WHEN Comment =  3 THEN 105  -- Subjective and argumentative
               WHEN Comment =  4 THEN 103  -- Not a real question
               WHEN Comment =  7 THEN 104  -- Too localized (poor mapping to new close reason)
               WHEN Comment = 10 THEN 104  -- General reference
               WHEN Comment = 20 THEN 103  -- Noise or pointless (Meta sites only)
               ELSE Comment
           END AS Comment
         , COUNT(Comment) AS Number
        FROM Questions
        GROUP BY QuestionDate, Comment
    UNION ALL
    SELECT QuestionDate
         , -1 -- Phony column for "Total"
         , COUNT(Id)
        FROM Questions
        GROUP BY QuestionDate
), ClosurePivotTableByQuestionDate AS (
    SELECT QuestionDate
         , [0] AS [Still Open]
         , [102] AS [Off-topic]
         , [103] AS [Unclear]
         , [104] AS [Too broad]
         , [105] AS [Opinion-based]
         , [-1] AS [Total]
        FROM (
            SELECT * FROM ClosureCommentByQuestionDate
        ) x PIVOT (
            SUM(Number)
            FOR Comment IN ([-1], [0], [102], [103], [104], [105])
        ) p
), ClosurePercentageByQuestionDate AS (
    SELECT QuestionDate
         , CAST([Still Open]    AS FLOAT) AS [Still Open]
         , CAST([Off-topic]     AS FLOAT) AS [Off-topic]
         , CAST([Unclear]       AS FLOAT) AS [Unclear]
         , CAST([Too broad]     AS FLOAT) AS [Too broad]
         , CAST([Opinion-based] AS FLOAT) AS [Opinion-based]
        FROM ClosurePivotTableByQuestionDate
)
SELECT *
    FROM ClosurePercentageByQuestionDate
    ORDER BY QuestionDate DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results