Q&A for experienced flyers, pilots, and builders of drones and model aircraft
WITH YearlyTagCounts AS ( SELECT TOP 5 t.TagName FROM PostLinks pl, PostTags pt, Tags t WHERE pl.PostId = 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) GROUP BY t.TagName ORDER BY COUNT(pl.PostId) DESC ), DailyTagPosts AS ( SELECT CAST(pl.CreationDate AS DATE) AS CDate, t.TagName, COUNT(pl.PostId) AS DailyPostCount FROM PostLinks pl, PostTags pt, Tags t, YearlyTagCounts ytc WHERE pl.PostId = 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) 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;