12_b

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

/*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('2015-3-10' as datetime) DateId
    UNION ALL
    SELECT DateId + 1
    FROM Date
    WHERE DateId + 1 <= '2015-03-18'
    )
    
select 
Top 50
c.PostId, Count(Id)
from Date d LEFT JOIN Comments c

on d.DateId=c.CreationDate

group by d.dateId, c.PostId
order by Count(c.Id) desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results