Unanswered Stats - Monthly

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

Вопросы и ответы для программистов

WITH 
Questions as 
    (
    SELECT *, DATEDIFF(m, CreationDate, GETDATE()) AS MonthAgo 
    FROM Posts WHERE PostTypeID = 1
    ),
UnansweredQuestions as 
  (SELECT * FROM Questions
  WHERE AcceptedAnswerID IS NULL),
Answers as (SELECT * FROM Posts WHERE PostTypeID = 2),
UpvotedAnswers as (SELECT * FROM Answers WHERE Score > 0),
NoPositiveAnswersCount as 
  (
  SELECT MonthAgo, COUNT(*) AS NPosCount FROM UnansweredQuestions
  LEFT JOIN UpvotedAnswers ON UnansweredQuestions.Id = UpvotedAnswers.ParentId
  WHERE UpvotedAnswers.Id IS NULL
  GROUP BY MonthAgo
  ),
QuestionsCount AS 
  (
  SELECT MonthAgo, COUNT(*) QCount FROM Questions
  GROUP BY MonthAgo
  ),
DeletedQuestions as 
    (
    SELECT *, DATEDIFF(m, CreationDate, GETDATE()) AS MonthAgo 
    FROM PostsWithDeleted WHERE PostTypeID = 1 AND DeletionDate IS NOT NULL
    ),
DeletedQuestionsCount AS 
  (
  SELECT MonthAgo, COUNT(*) DelCount FROM DeletedQuestions
  GROUP BY MonthAgo
  )
SELECT NoPositiveAnswersCount.MonthAgo, NPosCount, QCount, 
  CAST (((100 -  100.0 * NPosCount / QCount)) AS numeric(4,2)) AnswPr,
  DelCount,
  CAST (((100.0 * DelCount / (DelCount + QCount))) AS numeric(4,2)) DelPr
  FROM NoPositiveAnswersCount 
  INNER JOIN QuestionsCount
  ON NoPositiveAnswersCount.MonthAgo = QuestionsCount.MonthAgo
  INNER JOIN DeletedQuestionsCount
  ON DeletedQuestionsCount.MonthAgo = QuestionsCount.MonthAgo
  ORDER BY 1 ASC

Enter Parameters

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