0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for operations research and analytics professionals, educators, and students

-- final group
select eom
     , sum(q) as Questions
     , sum(a) as Answers
     , sum(dq) as [Deleted Questions]
     , sum(da) as [Deleted Answers]
     , sum(q+a+dq+da) as [Aggregate Traffic]

from 
(
-- all questions and answers 
select eomonth(creationdate) as eom
     , sum(case 
           when posttypeid = 1 then 1 
           else 0
          end
       ) as Q
     , sum(case 
           when posttypeid = 2 then 1 
           else 0 
           end
       ) as A
     , 0 as dq
     , 0 as da
from postswithdeleted p
group by eomonth(creationdate)
union 
-- when posts were deleted
select eomonth(deletiondate)
     , 0 
     , 0
     , sum(case 
           when posttypeid = 1 then 1 
           else 0
           end) as DQ
     , sum(case 
           when posttypeid = 2 then 1 
           else 0 
           end ) as DA
from postswithdeleted p
where p.deletiondate is not null
group by eomonth(deletiondate)
) as alldata
group by eom
order by eom

fork query

edited apr 20 Melanie.Shebel

created may 28 18 anonymous