0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

with top_tag as
(
select top 5 tg.tagName, tg.id
from posts po
join posttags pt on po.id = pt.postid
join tags tg on pt.tagid = tg.id
where YEAR(CreationDate) = YEAR(GETDATE())
group by tg.tagName, tg.id
order by count(*) desc
)

select cast(creationDate as date) as creationDate, tg.tagName,
count(*) as post_count,
sum(count(*)) over
(partition by tg.tagName order by cast(creationDate as date)) as cumulative
from posts po
join posttags pt on po.id = pt.postid
join top_tag tg on pt.tagid = tg.id
where YEAR(CreationDate) = YEAR(GETDATE())
group by cast(CreationDate as date), tg.tagName
order by cast(CreationDate as date)

fork query

edited oct 3 23

created oct 3 23 anonymous