Proportion of question upvotes to answer upvotes

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
Supporting data for hypothesis at
https://meta.stackoverflow.com/questions/371589/why-doesnt-a-question-earn-some-additional-reputation-when-its-answers-get-vote
*/

with q_a_rep as
(
  select q.id
    , count(*) as row_cnt
    , max(q.score) as q_rep -- Question score
    , sum(coalesce(a.score, 0)) as a_rep -- Answer score
    , sum(case when q.score > a.score then 1 else 0 end) as q_gt_a
  from
    (select
    id, title, viewcount, favoritecount, score, acceptedanswerid
    from posts 
    where score > 1
    and parentid is null -- is not an answer
    ) as q
  left join 
    (select
    id, parentid, body, viewcount, favoritecount, score
    from posts 
    ) as a
  -- on q.id = a.parentid -- Use this to get all answers rep
  on q.acceptedanswerid = a.id -- Use this for accepted answers
  group by q.id
  -- having max(q.score) > sum(a.score)
)

-- For high question scores, find how many questions scored better than answers
select
  q_rep
, sum(row_cnt)                     as cnt_a
, sum(q_gt_a)                      as cnt_q_gt_a
, 1.0 * sum(q_gt_a) / sum(row_cnt) as ratio_q_a
, avg(a_rep)                       as avg_a_rep
, min(a_rep)                       as min_a_rep
, max(a_rep)                       as max_a_rep
from q_a_rep
group by q_rep
order by q_rep desc

Enter Parameters

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