Statistics about the site questions and answers for the last 36 months

0

Please login or register to vote for this query.

(click on this box to dismiss)

Total number of questions and answers for the last 36 months (in 30 day chunks)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

-- Statistics about the site questions
-- Statistics about the site questions on a month basis

SET nocount ON 

CREATE TABLE #ranges (Id int identity, [start] datetime, [finish] datetime)

INSERT #ranges SELECT TOP 36 NULL, NULL FROM sysobjects

DECLARE @oldestPost dateTime

SELECT @oldestPost = CreationDate FROM Posts WHERE Id = (SELECT max(p2.Id) FROM Posts p2)

-- Look at 30 day chunks, so stats remain fairly accurate. 

UPDATE #ranges SET
  [start] = DateAdd(d, (0 - Id) * 30, @oldestPost),
  [finish] = DateAdd(d, (1 - Id) * 30, @oldestPost)



SELECT start,
  (SELECT count(*) FROM Posts WHERE ParentId IS NULL AND CreationDate BETWEEN [start] AND [finish]) AS [Total questions],
  (SELECT count(*) FROM Posts WHERE ParentId IS NULL AND ClosedDate IS NOT NULL AND CreationDate BETWEEN [start] AND [finish]) AS [Closed],
  (SELECT count(*) FROM Posts WHERE ParentId IS NULL AND Score > 5 AND CreationDate BETWEEN [start] AND [finish]) AS [Score 5 or higher],
  (SELECT count(*) FROM Posts WHERE ParentId IS NULL AND AcceptedAnswerId IS NOT NULL AND CreationDate BETWEEN [start] AND [finish]) AS [With accepted answers],
  (SELECT count(*) FROM Posts WHERE ParentId IS NULL AND AnswerCount > 5 AND CreationDate BETWEEN [start] AND [finish]) AS [With 5 or more answers],
  (SELECT count(*) FROM Posts WHERE ParentId IS NOT NULL AND Id IN (SELECT p2.AcceptedAnswerId FROM Posts p2 WHERE p2.ParentId IS NULL) AND Score > 1 AND CreationDate BETWEEN [start] AND [finish]) AS [Accepted answers score higher than 1],
  (SELECT count(*) FROM Posts WHERE ParentId IS NOT NULL AND CreationDate BETWEEN [start] AND [finish]) as [Total answers]
FROM #ranges

Enter Parameters

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