with calendar as ( select TOP(DATEDIFF(day, '2020-12-31',...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

with calendar as (
  select TOP(DATEDIFF(day, '2020-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 >= '2021-01-01 00:00:00'
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
    group by cal.d_date
            ,tg.TagName) pst_cnt

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results