Posts-Answers Change For a Single Month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

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  
--  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.CreationMonth = 12
ORDER BY CurrentAmountsByMonth.MonthIndex

Enter Parameters

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