Percentage of closed, deleted questions per month over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

WITH

q AS (
  SELECT
    Id,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    CASE
      WHEN DeletionDate IS NOT NULL THEN 'deleted'
      WHEN DeletionDate IS NULL AND ClosedDate IS NOT NULL THEN 'closed'
      ELSE 'open'
    END AS DeletionStatus
  FROM PostsWithDeleted
  WHERE PostTypeId=1),
p AS (
  SELECT Month, DeletionStatus, COUNT(*) AS N
  FROM  q
  GROUP BY Month, DeletionStatus
)
SELECT pDeleted.Month,
  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
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

Enter Parameters

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