Number of questions/answers/comments per day for the past 6 months


Please login or register to vote for this query.

(click on this box to dismiss)


Q&A for scientific skepticism

DECLARE @commentTypeId tinyint;  
SET @commentTypeId = 255;  

DECLARE @all_posts TABLE
    type tinyint null,
    date_created date null

insert into @all_posts
select  @commentTypeId type,
        DATEADD(day,0,DATEDIFF(day,0,c.CreationDate)) date_created
from    Comments c 
where   c.CreationDate >= DATEADD(MONTH, -6, GETDATE())

insert into @all_posts
select  PostTypeId type,
         DATEADD(day,0,DATEDIFF(day,0,CreationDate)) date_created
from    PostsWithDeleted
where   CreationDate >= DATEADD(MONTH, -6, GETDATE())
and     (PostTypeId = 1 or PostTypeId = 2)

select  date_created,
        SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS [Questions],
        SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS [Answers],
        SUM(CASE WHEN type = @commentTypeId THEN 1 ELSE 0 END) AS [Comments]
from    @all_posts
group by date_created
order by date_created

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results