rene's site activity

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

;with all_edits as (
select eomonth(creationdate) [month]
, sum(case when posthistorytypeid = 5 then 1 else 0 end) [edits]
from posthistory
where posthistorytypeid = 5
group by eomonth(creationdate)
)

select [month]
     , sum(q) [q]
     , sum(a) [a]
     , case 
       when sum(edits) > (select avg(edits) * 2 from all_edits)  
       then (select avg(edits) from all_edits)
       else sum(edits) 
       end [edits]
     , sum(reviews) [reviews]
     , sum([reviews completed]) [reviews completed]
from
(
select eomonth(creationdate) [month]
, sum(case when posttypeid = 1 then 1 else 0 end) [Q]
, sum(case when posttypeid = 2 then 1 else 0 end) [A]
, 0 [edits]
, 0 [reviews]
, 0 [reviews completed]
from posts
where posttypeid in (1,2)
group by eomonth(creationdate)
union all
select [month]
, 0
, 0
, [edits]
, 0
, 0
from all_edits
union all
select eomonth(creationdate)
     , 0
     , 0
     , 0
     , count(*)
     , sum(case when completedbyreviewtaskid is not null then 1 else 0 end) [reviews completed]
from reviewtasks
group by eomonth(creationdate)
) data
group by [month]
order by [month]

Enter Parameters

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