Q&A about the site for administrators and users of the CiviCRM Constituent Relationship Management software
/* 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 pl.CreationDate 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 pl.CreationDate, 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;*/ select Tags, count(tags) from posts group by tags order by count(tags) desc;