Q&A for ebook publishers and readers
-- Calculate number of questions per tag per year and total number of tags per year WITH QuestionsPerTag AS ( SELECT YEAR(p.CreationDate) AS Year, t.TagName, COUNT(*) AS QuestionsCount FROM Posts p JOIN PostTags pt ON p.Id = pt.PostId JOIN Tags t ON pt.TagId = t.Id WHERE p.PostTypeId = 1 -- Filter to include only questions and t.TagName = 'telegram-bot' GROUP BY YEAR(p.CreationDate), t.TagName ), TagsPerYear AS ( SELECT YEAR(p.CreationDate) AS Year, COUNT(DISTINCT t.TagName) AS TotalTags FROM Posts p JOIN PostTags pt ON p.Id = pt.PostId JOIN Tags t ON pt.TagId = t.Id WHERE p.PostTypeId = 1 -- Filter to include only questions GROUP BY t.TagName, YEAR(p.CreationDate) ) SELECT q.Year, q.TagName, q.QuestionsCount, t.TotalTags FROM QuestionsPerTag q JOIN TagsPerYear t ON q.Year = t.Year GROUP BY q.Year, q.TagName, q.QuestionsCount, t.TotalTags