Number of self-answered questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

select DATEPART(yyyy, q.CreationDate) as Year
     , DATEPART(q, q.CreationDate) as Quarter
     , count(distinct q.id) as Questions
     , count(distinct case when q.owneruserid = a.owneruserid 
                                then a.parentid
                           else null
                      end ) as [Self-answers]
     , count(distinct case when q.score > 0 and q.owneruserid = a.owneruserid 
                                then q.id
                           else null 
                      end) as [Q positive]
     , count(distinct case when a.score > 0  and q.owneruserid = a.owneruserid  
                                then a.parentid
                           else null
                       end) as [A positive]
  from posts q
  left outer join posts a
    on q.id = a.parentid
   and a.posttypeid = 2
 where q.posttypeid = 1
   and q.closeddate is null
 group by DATEPART(yyyy, q.CreationDate)
        , DATEPART(q, q.CreationDate)
 order by DATEPART(yyyy, q.CreationDate)
        , DATEPART(q, q.CreationDate)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached