Q&A about the site for information security professionals
declare @epoch as date = DateAdd(week, -1 - ##Weeks:int?52##, CURRENT_TIMESTAMP) declare @pivot as nvarchar(max) declare @query as nvarchar(max) ; select TOP ##TopTags:int?15## ROW_NUMBER() over (order by count(*) desc) as Rank, pt.TagId as TagId, t.TagName as TagName, count (*) as ZombieCount into #toptags from PostTags pt, Posts p, Tags t where (p.AnswerCount is null or p.AnswerCount = 0) and p.Id = pt.PostId and t.id = pt.TagId and p.PostTypeId = 1 and p.CommunityOwnedDate is null and p.ClosedDate is null group by pt.TagId, t.TagName order by count(*) desc insert into #toptags select 0, -1, 'All/10', count(*) from Posts p where (p.AnswerCount is null or p.AnswerCount = 0) and p.PostTypeId = 1 and p.CommunityOwnedDate is null and p.ClosedDate is null select distinct DateAdd(day, 7 - DatePart(weekday, CreationDate), Convert(Date, CreationDate)) as WeekEnd into #weekends from Users where CreationDate > @epoch select WeekEnd, TagName, Convert(decimal(10,2), count (p.Id)) as ZombieCount into #results from Posts p, PostTags pt, #weekends w, #toptags t where p.CreationDate < WeekEnd and p.PostTypeId = 1 and p.Id = pt.PostId and pt.TagId = t.TagId and p.CommunityOwnedDate is null and p.ClosedDate is null and WeekEnd < CURRENT_TIMESTAMP and not exists ( select 1 from Posts a where a.PostTypeId = 2 and a.ParentId = p.Id and a.CreationDate < WeekEnd) group by WeekEnd, TagName insert into #results select WeekEnd, TagName, Convert(decimal(10,2), count (p.Id)) / 10.0 as ZombieCount from Posts p, #weekends w, #toptags t where t.Rank = 0 and p.CreationDate < WeekEnd and p.PostTypeId = 1 and p.CommunityOwnedDate is null and p.ClosedDate is null and WeekEnd < CURRENT_TIMESTAMP and not exists ( select 1 from Posts a where a.PostTypeId = 2 and a.ParentId = p.Id and a.CreationDate < WeekEnd) group by WeekEnd, TagName SET @pivot = STUFF((SELECT ',' + QUOTENAME(TagName) FROM #toptags Order by Rank FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') ; print @pivot ; ; set @query = 'select * from #results pivot (sum(ZombieCount) for TagName in (' + @pivot + ')) as Pivoted order by WeekEnd ASC' ; execute(@query)