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 HasAnswer),
        2
    ) AS PercentByAnswered,
    CASE
        WHEN Visible = 'Yes' THEN
            ROUND(
                (100.0 * NumPosts) / SUM(NumPosts) OVER (PARTITION BY Visible),
                2
            )
        ELSE 0
    END 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 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 Visible, State, HasAnswer

Enter Parameters

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