new answers per month since 2018

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Ignore the datapoints for the current month. That data is not yet complete. Ex. Things like roomba have "lag"
SELECT
  DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1) AS Date,
  'average new non-deleted answers per new question' AS Type,
  CAST(SUM(CASE WHEN P.PostTypeId = 2 THEN 1 ELSE 0 END) AS REAL) /
  CAST(SUM(CASE WHEN P.PostTypeId = 1 THEN 1 ELSE 0 END) AS REAL) AS AverageNewAnswersPerNewQuestion
FROM PostsWithDeleted P
WHERE P.PostTypeId IN (1,2)
  AND YEAR(P.CreationDate) >= 2018
  AND (P.PostTypeId = 1 OR P.DeletionDate IS NULL)
GROUP BY DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1)
UNION
SELECT
  DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1) AS Date,
  'average new deleted answers per new question' AS Type,
  CAST(SUM(CASE WHEN P.PostTypeId = 2 THEN 1 ELSE 0 END) AS REAL) /
  CAST(SUM(CASE WHEN P.PostTypeId = 1 THEN 1 ELSE 0 END) AS REAL) AS AverageNewAnswersPerNewQuestion
FROM PostsWithDeleted P
WHERE P.PostTypeId IN (1,2)
  AND YEAR(P.CreationDate) >= 2018
  AND (P.PostTypeId = 1 OR P.DeletionDate IS NOT NULL)
GROUP BY DATEFROMPARTS(YEAR(P.CreationDate), MONTH(P.CreationDate), 1)
ORDER BY Date

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results