Statistical differences between self and non-self answered questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

;with self_answered_q as
(
select q.id, q.owneruserid, q.creationdate
from posts q
inner join posts a on a.parentid = q.id
where q.owneruserid = a.owneruserid
and q.creationdate = a.creationdate
and q.answercount > 1
),
other_q as (
  select id
  from posts
  where posttypeid = 1 -- Q
  and answercount > 1
  Except 
  select id 
  from self_answered_q
),
self_answer_stats as (
  select avg(convert(decimal,answercount))   [avg]
       , max(answercount) [max]
   from posts q 
   inner join self_answered_q sa on sa.id = q.id
),
other_stats as (
   select avg(convert(decimal,answercount)) [avg]
       , max(answercount) [max]
   from posts q 
   inner join other_q sa on sa.id = q.id
),
first_self as (
select a.parentid id, min(datediff(mi, q.creationdate,  a.creationdate)) [first_answer]
  from posts a 
  inner join self_answered_q q on q.id = a.parentid
  where a.owneruserid <>  q.owneruserid
  and a.creationdate > q.creationdate -- no merges
  group by a.parentid
),
first_other as (
  select a.parentid id, min(datediff(mi, rq.creationdate,  a.creationdate)) [first_answer]
  from posts a 
  inner join other_q q on q.id = a.parentid
  inner join posts rq on rq.id = a.parentid
  where a.creationdate > rq.creationdate -- no merges
  group by a.parentid
)

select 'self answer'
     , [avg] [avg answercount]
     , [max]
     , (select avg(convert(decimal,first_answer)/(60))
        from first_self) [avg hrs to 1st A]
        , (select min(convert(decimal,first_answer))
        from first_self) [min minutes to 1st A]
from self_answer_stats
union all
select 'not self answer'
     , [avg]
     , [max]
     , (select avg(convert(decimal,first_answer)/(60))
        from first_other) [avg hrs to 1st A]
        , (select min(convert(decimal,first_answer))
        from first_other) [min minutes to 1st A]
from other_stats

Enter Parameters

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