Shows the trend for your favourite tag
Q&A about the site for gardeners and landscapers
-- How many posts per month for a tag? -- Shows the trend for your favourite tag DECLARE @TagName varchar(255) = '##TagName##'; DECLARE @PostCountByMonth TABLE ( [date] datetime, [count] int, UNIQUE CLUSTERED ([date]) ); INSERT INTO @PostCountByMonth SELECT dateadd(month, DATEDIFF(month, '', p.CreationDate), '') [date], count(*) [count] FROM Posts p WHERE p.Tags LIKE '%<##TagName##>%' GROUP BY DATEDIFF(month, '', p.CreationDate) ORDER BY DATEDIFF(month, '', p.CreationDate); SELECT datepart(year, p1.[date]) [year], datepart(month, p1.[date]) [month], sum(p2.[count]) [trend] FROM @PostCountByMonth p1 INNER JOIN @PostCountByMonth p2 ON p1.[date] >= p2.[date] GROUP BY p1.[date]