Percentage of closure/deletion events compared to the creation events per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

WITH Raw AS (
  SELECT 'Created' AS EventType, Id, CreationDate AS TS
  FROM PostsWithDeleted
  WHERE PostTypeId = 1

  UNION

  SELECT 'Closed' AS EventType, Id, ClosedDate AS TS
  FROM PostsWithDeleted
  WHERE ClosedDate IS NOT NULL AND PostTypeId = 1

  UNION

  SELECT 'Deleted' AS EventType, Id, DeletionDate AS TS
  FROM PostsWithDeleted
  WHERE DeletionDate IS NOT NULL AND PostTypeId = 1
),
Raw2 AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, TS),
      DATEPART(m, TS),
      1) AS Month,
    EventType,
    COUNT(*) AS Count
  FROM Raw
  GROUP BY EventType,
    DATEFROMPARTS(
      DATEPART(yyyy, TS),
      DATEPART(m, TS),
      1)
)
SELECT
  Cr.Month,
  100.0 * Cl.Count / Cr.Count AS ClosedPct,
  100.0 * De.Count / Cr.Count AS DeletedPct
FROM Raw2 Cr, Raw2 Cl, Raw2 De
WHERE
  Cr.Month = Cl.Month
  AND
  Cr.EventType = 'Created'
  AND
  Cl.EventType = 'Closed'
  AND
  Cr.Month = De.Month
  AND
  De.EventType = 'Deleted'
ORDER BY Cr.Month

Enter Parameters

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