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