Q&A for speakers of other languages learning English
DECLARE @CurrentYear INT = YEAR(GETDATE()); WITH TopTags AS ( SELECT TOP 5 t.TagName, COUNT(*) AS PostCount FROM Posts p JOIN PostTags pt ON p.Id = pt.PostId JOIN Tags t ON t.Id = pt.TagId WHERE YEAR(p.CreationDate) = @CurrentYear GROUP BY t.TagName ORDER BY PostCount DESC ), DailyPosts AS ( SELECT CAST(p.CreationDate AS DATE) AS Date, t.TagName, COUNT(*) AS DailyPostCount FROM Posts p JOIN PostTags pt ON p.Id = pt.PostId JOIN Tags t ON t.Id = pt.TagId WHERE YEAR(p.CreationDate) = @CurrentYear AND t.TagName IN (SELECT TagName FROM TopTags) GROUP BY CAST(p.CreationDate AS DATE), t.TagName ) SELECT dp.Date, dp.TagName, SUM(dp.DailyPostCount) OVER (PARTITION BY dp.TagName ORDER BY dp.Date) AS CumulativePostCount FROM DailyPosts dp ORDER BY dp.TagName, dp.Date;