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

select 
A.PostId as PostId,
D.DateId as Date,
count(C1.Id) as CommentCountPerPostPerday


from
(select
top 50
C.PostId
from
Comments C
where DATEDIFF(day,C.CreationDate , GETDATE()) <= 365
group by C.PostId
order by count(C.Id) desc
) A
---Getting top 50 posts of the year

join

Comments C1
on A.PostId = C1.PostId
---Joining to get all comments on these top 50 posts of the year irrespecive of when they were made.

join

(
select
C2.CreationDate,
CONVERT(date, C2.CreationDate) as DateId 
from Comments C2
) D
on C1.CreationDate = D.CreationDate
----Replicating the date dimension table

group by
A.PostId,
D.DateId

order by
D.DateId
;

fork query

edited feb 3

created feb 2 anonymous