Questions Statistics

2

Please login or register to vote for this query.

(click on this box to dismiss)

This query returns statistics about the site questions, on a month basis.

Movies and TV

Q&A for movie and tv enthusiasts

-- 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 12 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]
FROM #ranges

Enter Parameters

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