Percentage of the dupe questions by month

2

Please login or register to vote for this query.

(click on this box to dismiss)

It counts also the deleted questions.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    0 AS EventType,
    Id
  FROM PostsWithDeleted
  WHERE PostTypeId = 1

  UNION

  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, ClosedDate),
      DATEPART(mm, ClosedDate),
      1) AS Month,
    1 AS EventType,
    Id
  FROM PostsWithDeleted
  WHERE
    Id IN (SELECT PostId FROM PostLinks WHERE LinkTypeId = 3)
    AND
    ClosedDate IS NOT NULL
    AND
    PostTypeId = 1
)
SELECT
  Month,
  /*
  COUNT(CASE WHEN EventType = 0 THEN 1 ELSE NULL END) AS Created,
  COUNT(CASE WHEN EventType = 1 THEN 1 ELSE NULL END) AS ClosedAsDupe
  */
  100.0 * COUNT(CASE WHEN EventType = 1 THEN 1 ELSE NULL END) /
    COUNT(CASE WHEN EventType = 0 THEN 1 ELSE NULL END) AS ClosedAsDupePct
FROM Raw
GROUP BY Month
ORDER BY Month DESC

Enter Parameters

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