0

Please login or register to vote for this query.

(click on this box to dismiss)

Borrowed the week construct from https://data.stackexchange.com/stackoverflow/query/208831/total-downvotes-over-time#graph

Q&A for professional and enthusiast programmers

select dateadd(week, datediff(week, 0, v.CreationDate), 0) wk
, sum(case votetypeid when 2 then 1 when 3 then 1 else 0 end ) as total_votes
, sum(case votetypeid when 2 then 1 else 0 end ) as upvotes
, sum(case votetypeid when 3 then 1 else 0 end ) as downvotes
, convert( float, sum(case votetypeid when 3 then 1 else 0 end ) ) /
  sum(case votetypeid when 3 then 1 when 2 then 1 else 0 end ) as downvote_fraction
from votes v
-- inner join votetypes vt on vt.id = v.votetypeid 
where v.CreationDate > dateadd(year, 0, '2013-01-01')
group by  dateadd(week, datediff(week, 0, v.CreationDate), 0) 
order by dateadd(week, datediff(week, 0, v.CreationDate), 0)

view current version

edited jul 21 14 martin clayton

created jul 16 14 rene