Answered Count by Post State

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics Meta

Q&A about the site for people studying math at any level and professionals in related fields

SELECT
    *,
    ROUND((100.0 * NumPosts) / SUM(NumPosts) OVER (PARTITION BY State), 2) AS PercentOfState,
    ROUND((100.0 * NumPosts) / SUM(NumPosts) OVER (PARTITION BY State), 2) AS PercentOfVisible,
    ROUND((100.0 * NumPosts) / SUM(NumPosts) OVER (), 2) AS PercentAll
FROM (
    SELECT
        HasAnswer,
        State,
        Visible,
        COUNT(*) AS NumPosts
    FROM (
        SELECT
            Questions.Id,
            CASE 
                WHEN COUNT(Answers.Id) >= 1 THEN 'Yes'
                ELSE 'No'
            END AS HasAnswer,
            CASE
                WHEN Questions.DeletionDate IS NOT NULL THEN 'Deleted'
                WHEN Questions.ClosedDate IS NOT NULL THEN 'Closed'
                ELSE 'Open'
            END AS State,
            CASE
                WHEN Questions.DeletionDate IS NOT NULL THEN 'Yes'
                ELSE 'No'
            END AS Visible
        FROM PostsWithDeleted AS Questions
        LEFT JOIN PostsWithDeleted AS Answers ON (
            Answers.ParentId = Questions.Id
            AND Answers.PostTypeId = 2
        )
        WHERE Questions.PostTypeId = 1
        GROUP BY Questions.Id, Questions.DeletionDate, Questions.ClosedDate
    ) AS QData
    GROUP BY HasAnswer, State, Visible
) AS QCounts
ORDER BY HasAnswer, Visible, State

Enter Parameters

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