Q&A about the site for developers and users of elementary OS and applications
-- Orginal query from: https://data.stackexchange.com/stackoverflow/query/470711/tag-size-and-growth-trends DECLARE @TagName NVARCHAR(25) = ##TagName:string?azure-devops##, @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;