Q&A for amateur radio enthusiasts
WITH YearlyTagCounts AS ( SELECT TOP 5 t.TagName FROM Posts pl, PostTags pt, Tags t WHERE pl.id = pt.PostId AND pt.TagId = t.Id AND pl.CreationDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND pl.CreationDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) and pl.Id >= 77734096 GROUP BY t.TagName ORDER BY COUNT(pl.id) DESC ), DailyTagPosts AS ( SELECT CAST(pl.CreationDate AS DATE) AS CDate, t.TagName, COUNT(pl.id) AS DailyPostCount FROM Posts pl, PostTags pt, Tags t, YearlyTagCounts ytc WHERE pl.id = pt.PostId AND pt.TagId = t.Id AND t.TagName = ytc.TagName AND pl.CreationDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND pl.CreationDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) and pl.Id >= 77734096 GROUP BY CAST(pl.CreationDate AS DATE), t.TagName ), CumulativeTagPosts AS ( SELECT CDate, TagName, SUM(DailyPostCount) OVER (PARTITION BY TagName ORDER BY CDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativePosts FROM DailyTagPosts ) SELECT CDate, TagName AS Theme, CumulativePosts AS CumulativeNumberOfPosts FROM CumulativeTagPosts ORDER BY TagName, CDate;