1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and learners wanting to discuss the finer points of the Portuguese language

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

fork query

edited may 28 19

created may 28 19 peterh (2)