0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the ratio of answer votes to question votes in the last 50 weeks, clearly demonstrates the effects of the electorate badge

Q&A about the site for people interested in the history and origins of science and mathematics

-- Amount of Question Votes compared to Answer Votes
-- Shows the ratio of answer votes to question votes in the last 50 weeks, clearly demonstrates the effects of the electorate badge

set nocount on

declare @ranges table (id int identity, start datetime, finish datetime)
declare @now datetime
set @now = GetDate()

insert @ranges
select top 50 null,null from Posts


update @ranges
set start = DateAdd(ww,-1 - id,@now), finish = DateAdd(ww,0 - id, @now)  


set nocount off

select   start,
         sum(case when ParentId is null then 1 else 0 end)/ (sum(case when ParentId is null then 1 else 0 end) + sum(case when ParentId is not null then 1 else 0 end)+ 0.0) as ratio,
         sum(case when ParentId is null then 1 else 0 end) as [question votes],
         sum(case when ParentId is not null then 1 else 0 end) as [answer votes]
from votes
join @ranges on CreationDate >= start and CreationDate < finish
join Posts p on PostId = p.id
where VoteTypeId = 2
group by start, finish
order by start asc

view current version

edited sep 17 10

created sep 17 10 Daniel Vandersluis