Q&A about the site for biology researchers, academics, and students
/* 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; WITH PostTagCombinations AS ( SELECT pt.PostId, STRING_AGG(t.TagName, ',') WITHIN GROUP (ORDER BY t.TagName) AS TagCombination FROM PostTags pt, Tags t WHERE pt.TagId = t.Id GROUP BY pt.PostId ), CombinationCounts AS ( SELECT TagCombination, COUNT(*) AS CombinationCount FROM PostTagCombinations GROUP BY TagCombination ) SELECT TOP 10 TagCombination, CombinationCount FROM CombinationCounts ORDER BY CombinationCount DESC; select count(p.PostId) from PostLinks p, PostsWithDeleted pwd where p.PostId = pwd.AcceptedAnswerId*/ Select * from VoteTypes