0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

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

view current version

edited nov 8

created nov 8 Janis123