Answered Count by Post State

0

Please login or register to vote for this query.

(click on this box to dismiss)

Japanese Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

SELECT
    *,
    ROUND(
        (100.0 * NumPosts) / SUM(NumPosts) OVER (PARTITION BY HasAnswer),
        2
    ) AS PercentByAnswered,
    ROUND(
        (100.0 * NumPosts) / SUM(NumPosts) OVER (PARTITION BY State),
        2
    ) AS PercentOfState,
    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