Winterbash 2014 "Red Shirt" Estimation
I find this pretty nicely done and really hard to pick on. 
But here's a little something.
Instead of writing this condition this way:


WHERE Posts.CreationDate &lt;= @today
  AND Posts.CreationDate &gt; @target_week



This is shorter and simpler:

WHERE Posts.CreationDate BETWEEN @target_week AND @today


Keep in mind though that's not exactly the same,
because if I remember correctly,
BETWEEN uses &lt;= and &gt;= conditions.
But even if so, it might not make a difference anyway in the case of this query.
(I confirmed with 1 week to go back, the results are identical.)

Btw, @Malachi pointed out an excellent article about range queries and their dangers.

A very minor nitpick, but in this column alias I would spell out the "dvote" nicely as "downvote":


  SELECT Votes.PostId AS dvote