Unanswered Stats - Monthly


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

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

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,
  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

Enter Parameters

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