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 UNION ALL SELECT DATEADD(day, 1, DateId) FROM Date WHERE DATEADD(day, 1, DateId) <= '2012-7-25' ) , Comments2 as ( SELECT Id,PostId,CONVERT(date, CreationDate) as cDate FROM Comments ) select Top 50 d.DateId, c.PostId, Count(c.Id) as CommentCount from Date d LEFT JOIN Comments2 c on d.DateId=c.cDate 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