1

Please login or register to vote for this query.

(click on this box to dismiss)

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;

fork query

edited aug 21 19

created dec 11 17 Anisha Pindoria