12

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results