Q&A for professional and enthusiast programmers
/*12. You are interested in looking at the Comment activity
at a Daily level the hottest topics of the past year.
Assuming you have a Date dimension table (Date) which is a table with all days
going from 1990 to 2999 (Column Name: dateId), create a query that returns the
number of comments made on each day
for each post from the top 50 most commented on posts in the past year.*/
with Date as (
SELECT CAST('2012-6-20' as date) DateId
SELECT DATEADD(day, 1, DateId)
WHERE DATEADD(day, 1, DateId) <= '2012-7-25'
Comments2 as (
SELECT Id,PostId,CONVERT(date, CreationDate) as cDate
d.DateId, c.PostId, Count(c.Id) as CommentCount
from Date d LEFT JOIN Comments2 c
where d.DateId <= '2012-07-25' AND d.DateId >= '2012-07-01'
group by d.DateId, c.PostId
order by d.DateId asc, Count(c.Id) desc