0

Please login or register to vote for this query.

(click on this box to dismiss)

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

SELECT
    *,
    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 HasAnswer, Visible, State

view current version

edited nov 17

created nov 17 jpmc2631240