Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation
--WITH q AS (
1) AS Month,
COUNT(*) AS AllQuestions,
COUNT(DeletionDate) AS Deleted,
COUNT(CASE WHEN DeletionDate IS NULL AND ClosedDate IS NOT NULL THEN 1 ELSE NULL END) AS Closed/*
WHEN DeletionDate IS NOT NULL THEN 'deleted'
WHEN DeletionDate IS NULL AND ClosedDate IS NOT NULL THEN 'closed'
END AS DeletionStatus*/
GROUP BY DATEFROMPARTS(
p AS (
SELECT Month, DeletionStatus, COUNT(*) AS N
GROUP BY Month, DeletionStatus
pDeleted.N + pClosed.N + pOpen.N AS 'All',
pDeleted.N + pClosed.N AS Closed,
pDeleted.N AS 'Deleted'
FROM p AS pDeleted, p AS pClosed, p AS pOpen
pDeleted.Month = pOpen.Month
AND pDeleted.Month = pClosed.Month
AND pDeleted.DeletionStatus = 'deleted'
AND pClosed.DeletionStatus = 'closed'
AND pOpen.DeletionStatus = 'open'
ORDER BY Month DESC*/