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,
CONVERT(date, C1.CreationDate) as DateId,
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
---Joining to get all comments on these top 50 posts of the year irrespecive of when they were made.
Comments C1
on A.PostId = C1.PostId

group by A.PostId,CONVERT(date, C1.CreationDate)
order by A.PostId,CONVERT(date, C1.CreationDate)
;

fork query

edited feb 3

created feb 2 anonymous