Counts and Scores and Averages for Questions and Answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Worldbuilding

Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

select ownerUserId as [User Link],
       question_count,
       question_score,
       -- case statement to avoid dividing by zero
       (case when question_count > 0 then (1.0*question_score/question_count) else 0 end) as score_per_question,
       answer_count,
       answer_score,
       -- case statement to avoid dividing by zero
       (case when answer_count > 0 then (1.0*answer_score/answer_count) else 0 end) as score_per_answer
from
(select p.ownerUserId, 
sum(case when p.postTypeId=1 then 1 else 0 end) question_count,
sum(case when p.postTypeId=1 then p.score else 0 end) question_score,
sum(case when p.postTypeId=2 then 1 else 0 end) answer_count,
sum(case when p.postTypeId=2 then p.score else 0 end) answer_score
from posts p
join users u
on p.ownerUserId = u.Id
where u.reputation > 500 -- to limit the rows (and allow sorting)
group by p.ownerUserId) a

Enter Parameters

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