Comparison of # of posts per period

2

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

CREATE TABLE #results (site NVARCHAR(250),
  QuestionsLastWeek INT,
  QuestionsComparedWeek INT,
  MoreQuestions FLOAT,
  AnswersLastWeek INT,
  AnswersComparedWeek INT,
  MoreAnswers FLOAT);
  
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''',
  QuestionsLastWeek, QuestionsComparedWeek,
  CASE QuestionsComparedWeek WHEN 0 THEN NULL ELSE
  ROUND((QuestionsLastWeek - QuestionsComparedWeek) * 100.0 / QuestionsComparedWeek, 2) END,
  AnswersLastWeek, AnswersComparedWeek,
  CASE AnswersComparedWeek WHEN 0 THEN NULL ELSE
  ROUND((AnswersLastWeek - AnswersComparedWeek) * 100.0 / AnswersComparedWeek, 2) END
FROM (
SELECT (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Posts
    WHERE PostTypeId = 1 -- Questions
      AND CreationDate BETWEEN ''' + ##beginOfPeriod:string## + ''' AND ''' + ##endOfPeriod:string## + '''
) AS QuestionsLastWeek, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Posts
    WHERE PostTypeId = 1 -- Questions
      AND CreationDate BETWEEN ''' + ##beginOfComparedPeriod:string## + ''' AND ''' + ##endOfComparedPeriod:string## + '''
) AS QuestionsComparedWeek, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Posts
    WHERE PostTypeId = 2 -- Answers
      AND CreationDate BETWEEN ''' + ##beginOfPeriod:string## + ''' AND ''' + ##endOfPeriod:string## + '''
) AS AnswersLastWeek, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Posts
    WHERE PostTypeId = 2 -- Answers
      AND CreationDate BETWEEN ''' + ##beginOfComparedPeriod:string## + ''' AND ''' + ##endOfComparedPeriod:string## + '''
) AS AnswersComparedWeek
) AS Temp
'
FROM sys.databases
WHERE database_id > 5 AND name NOT LIKE '%.Meta';

EXEC (@sql);

SELECT * FROM (
SELECT '_TOTAL' AS 'site',
  SUM(QuestionsLastWeek) AS '# questions in period',
  SUM(QuestionsComparedWeek) AS '# questions compared',
  ROUND((SUM(QuestionsLastWeek) - SUM(QuestionsComparedWeek)) * 100.0 / SUM(QuestionsComparedWeek), 2) AS '% more questions',
  SUM(AnswersLastWeek) AS '# answers in period',
  SUM(AnswersComparedWeek) AS '# answers compared',
  ROUND((SUM(AnswersLastWeek) - SUM(AnswersComparedWeek)) * 100.0 / SUM(AnswersComparedWeek), 2) AS '% more answers'
  FROM #results
UNION ALL
SELECT * FROM #results
) AS Temp ORDER BY SUBSTRING(site, 1, 1), 4 DESC;
  
DROP TABLE #results;

Enter Parameters

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