Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation
q AS (
1) AS Month,
WHEN DeletionDate IS NOT NULL THEN 'deleted'
WHEN DeletionDate IS NULL AND ClosedDate IS NOT NULL THEN 'closed'
END AS DeletionStatus
WHERE PostTypeId IN (1,2)),
p AS (
SELECT Month, DeletionStatus, COUNT(*) AS N
GROUP BY Month, DeletionStatus)
pDeleted.N AS Deleted, pClosed.N AS Closed, pOpen.N AS 'Open'
FROM p AS pDeleted, p AS pClosed, p AS pOpen
WHERE 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