0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

WITH amounts_by_month (
  MonthIndex, 
  PostAmounts, 
  CreationMonth, 
  CreationYear) 
AS (
  SELECT 
    MonthIndex, 
    SUM(PostsAnswersCount) AS TotalPosts, 
    CreationMonth, 
    CreationYear
  FROM (
    SELECT 
      Posts.Id AS CurrentId, 
      Posts.Title AS CurrentTitle, 
      1 + Posts.AnswerCount AS PostsAnswersCount, 
      MONTH(Posts.CreationDate) AS CreationMonth, 
      YEAR(Posts.CreationDate) AS CreationYear,
      DATEDIFF(month, '2008-07-01', CreationDate) AS MonthIndex
    FROM Posts
    WHERE Posts.PostTypeId = 1
  ) AS PostsAnswersCounts
  GROUP BY MonthIndex, CreationYear, CreationMonth
)

SELECT CreationMonth, AVG(PostsAmountChange) AS AverageActivityChange
FROM
(SELECT  
--  CurrentAmountsByMonth.MonthIndex AS CurrentMonthIndex,
--  PreviousAmountsByMonth.MonthIndex AS PreviousMonthIndex,
--  CurrentAmountsByMonth.PostAmounts AS CurrentPosts,
--  PreviousAmountsByMonth.PostAmounts AS PreviousPosts,
  CurrentAmountsByMonth.CreationMonth AS CreationMonth,
  CurrentAmountsByMonth.CreationYear AS CreationYear,
  CAST(
    (CurrentAmountsByMonth.PostAmounts / 
    CAST(PreviousAmountsByMonth.PostAmounts AS DECIMAL(15,6))) - 1
  AS 
    DECIMAL(15,4)
  ) AS PostsAmountChange
FROM amounts_by_month AS CurrentAmountsByMonth
LEFT JOIN amounts_by_month AS PreviousAmountsByMonth
ON CurrentAmountsByMonth.MonthIndex - 1 = PreviousAmountsByMonth.MonthIndex
WHERE CurrentAmountsByMonth.MonthIndex > 1
) AS MonthChanges
GROUP BY CreationMonth
ORDER BY CreationMonth

fork query

edited oct 22 20

created oct 22 20 jon.doe34646