2

Please login or register to vote for this query.

(click on this box to dismiss)

Enter a tag and a time range to see the growth in posts using that tag over time, grouped by calendar months by default. Set the mode parameter to 1 if you want to group by calendar months, but aligned to last dump day instead of calendar boundaries; or 2 to group by 30-day periods ('months') aligned to last dump day.

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

-- Note from editor: Don't let the original author of this query anywhere
-- near your databases. If you were considering letting them anywhere near
-- your databases, look at the revision history of this query first. (And
-- if you haven't time for that, just look at the previous revision:
-- <http://data.stackexchange.com/stackoverflow/revision/90306/145532/>.)
-- Defend your database servers with sharp objects if necessary. That's how
-- bad the previous iterations of this query were. (And if you couldn't
-- figure that out on your own, you don't deserve to be anywhere near a
-- database either.)

DECLARE @TagName NVARCHAR(25) = ##TagName:string?vs-team-services##,
        @MinMonthsAgo INT = ##MinMonthsAgo:int?0##,
        @MaxMonthsAgo INT = ##MaxMonthsAgo:int?12##,
        @GroupingMode TINYINT = ##GroupingMode:int?0##,
        @CurrentDate DATE = GETDATE();

WITH PostsWithTag(MonthsAgo, IsQ, IsA) AS (
  SELECT
    CASE @GroupingMode
     WHEN 0 THEN DATEDIFF(month, P.CreationDate, @CurrentDate)
     WHEN 1 THEN DATEDIFF(month, P.CreationDate, @CurrentDate)
     WHEN 2 THEN DATEDIFF(day, P.CreationDate, @CurrentDate) / 30
    END - CASE
     -- bother DATEDIFF and its odd month behaviour
     -- http://www.sqlteam.com/article/datediff-function-demystified
     WHEN @GroupingMode = 1
      AND DATEPART(day, P.CreationDate) > DATEPART(day, @CurrentDate)
     THEN 1 ELSE 0
    END,
    CASE PostTypeId WHEN 1 THEN 1 ELSE NULL END,
    CASE PostTypeId WHEN 2 THEN 1 ELSE NULL END
  FROM Posts AS P
  INNER JOIN PostTags AS PT
    ON PT.PostId IN (P.Id, P.ParentId)
  WHERE PT.TagId = (SELECT Id FROM Tags
                    WHERE LOWER(TagName) = LOWER(@TagName))
)
SELECT
  CASE MonthsAgo WHEN 0 THEN 'this month'
                 WHEN 1 THEN 'last month'
                 ELSE CONCAT(MonthsAgo, ' months ago') END AS [Date],
  COUNT(*) AS [Post Count],
  -- (m - n)/n = m/n - n/n = m/n - 1
  FORMAT(CAST(COUNT(*) AS FLOAT)
         / LAG(COUNT(*)) OVER (ORDER BY MonthsAgo DESC)
         - 1, '+##0.##%;-##0.##%') AS [Post Change],
  COUNT(IsQ) AS [Question Count],
  -- (m - n)/n = m/n - n/n = m/n - 1
  FORMAT(CAST(COUNT(IsQ) AS FLOAT)
         / LAG(COUNT(IsQ)) OVER (ORDER BY MonthsAgo DESC)
         - 1, '+##0.##%;-##0.##%') AS [Question Change],
  COUNT(IsA) AS [Answer Count],
  -- (m - n)/n = m/n - n/n = m/n - 1
  FORMAT(CAST(COUNT(IsA) AS FLOAT)
         / LAG(COUNT(IsA)) OVER (ORDER BY MonthsAgo DESC)
         - 1, '+##0.##%;-##0.##%') AS [Answer Change]
FROM PostsWithTag
WHERE MonthsAgo BETWEEN @MinMonthsAgo AND @MaxMonthsAgo
GROUP BY MonthsAgo
ORDER BY MonthsAgo DESC;

fork query

edited apr 12 16 Anisha Pindoria

created dec 29 12 Lee Whitney