Q&A for students, researchers and practitioners of computer science
with calendar as ( select TOP(DATEDIFF(day, '2023-12-31', CONVERT (date, GETDATE()))) DATEADD(DD,1-ROW_NUMBER() OVER (ORDER BY Id), CONVERT (date, GETDATE())) as d_date from Posts) ,top_tags as ( select top(5) ptg.TagId from Posts pst join PostTags ptg on pst.Id = ptg.PostId where pst.CreationDate >= '2024-01-01 00:00:00' and pst.Id >= 77734096 group by ptg.TagId order by count(1) desc) select pst_cnt.d_date ,pst_cnt.TagName ,sum(pst_cnt.cnt_posts) over (partition by TagName order by d_date rows between unbounded preceding and current row) from (select cal.d_date ,tg.TagName ,count(distinct pst.Id) cnt_posts from calendar cal cross join top_tags ttg join Tags tg on ttg.TagId = tg.Id join PostTags pttg on pttg.TagId = ttg.TagId join Posts pst on pst.Id = pttg.PostId and CONVERT (date, pst.CreationDate) = cal.d_date and pst.Id >= 77734096 group by cal.d_date ,tg.TagName) pst_cnt --3,16,9,92497,17 -- AND pst.Id >= 77734096 --Including Id would speed up request significantly