;with PostsEditedByOp as ( select distinct postid from po...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chemistry

Q&A for scientists, academics, teachers, and students in the field of chemistry

;with PostsEditedByOp as 
(
 select distinct postid
 from posthistory ph
 inner join posts p on p.id  = ph.postid
 where posthistorytypeid in (4,5,6,7,8,9)  -- edits / rollback
 and (owneruserid = ph.userid)
)

select (select count(*) from posts where posttypeid in (1,2)) [total posts]
     , (select count(*) from posts where posttypeid = 1) [total questions]
     , (select count(*) from posts where posttypeid = 2) [total answers]
     , count(*) [# with comments, not OP] 
     , sum(case posttypeid when 1 then 1 else 0 end) [# questions]
     , sum(case when posttypeid = 1 and closeddate is not null then 1 else 0 end) [# closed]
     , sum(case posttypeid when 2 then 1 else 0 end) [# answers]
     , sum(case when pe.postid is null then 1 else 0 end) [OP did edit post]
from posts p
left outer join PostsEditedByOp pe on pe.postid = p.id
where posttypeid in (1,2)
and commentcount > 0
and not exists 
  ( select *
    from
    comments c 
    where c.postid = p.id
    and c.userid <> p.owneruserid)

Enter Parameters

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