Winterbash 2014 "Red Shirt" Estimation

Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression.

Try it here!

Background


Red Shirt


cast 5 downvotes on posts that are later deleted or closed

Limitations

There are certain Stack Exchange limitations which make querying this information a bit tricky. Namely:

-
The data is only refreshed once a week, on Sundays. This makes it impossible to have "real time" results.

-
User voting activity is anonymous, in that a user can only see their own voting activity in their own profile. This disallows joining voting and user data on SEDE.

Assumptions

I have made certain assumptions, based on the trends I generally see on closed questions. They are:

-
A user who votes to close/delete a bad question will usually also downvote the question.

-
A user who downvotes a question will usually do so before voting to close/delete.

Usage

To get usefulness out of this (and as indicated in the SQL comments at the top of the query):


The way that this report can be used is by comparing the results set
side-by-side with your "votes" under your activity reports.
Filter by down-votes and look to see if questions you down-voted are
in the result set below.

For example:

Query

```
/*
Winter Bash 2014
"Red Shirt" hat estimation
"Cast 5 downvotes on posts that are later deleted or closed"

The way that this report can be used is by comparing the results set
side-by-side with your "votes" under your activity reports.
Filter by down-votes and look to see if questions you down-voted are
in the result set below.
*/

-- NumberWeeks: Number of weeks to go back

-- DATETIME VARIABLES

DECLARE @today DATETIME;
SET @today = CURRENT_TIMESTAMP;

DECLARE @weeks_ago INT;
SET @weeks_ago = ##NumberWeeks:int?4##;
-- Number of weeks must not go into the future, hence the following:
SET @weeks_ago = (CASE WHEN @weeks_ago >0 THEN -@weeks_ago ELSE @weeks_ago END);

DECLARE @target_week DA

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  @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 = 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

StackExchange Code Review Q#74205, answer score: 14