Answered Count by Post State

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity Meta

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

SELECT
    HasAnswer,
    State,
    COUNT(*) AS NumPosts,
    ROUND((100.0 * COUNT(*)) / SUM(COUNT(*)) OVER (), 2) AS PercentPosts
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
    FROM PostsWithDeleted AS Questions
    LEFT JOIN PostsWithDeleted AS Answers ON Answers.Id = Questions.ParentId
    WHERE
        Questions.PostTypeId = 1
        AND Answers.PostTypeId = 2
    GROUP BY Questions.Id, Questions.DeletionDate, Questions.ClosedDate
) AS QData
GROUP BY HasAnswer, State
ORDER BY HasAnswer, State

Enter Parameters

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