Red Shirt hat progression

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

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

-- 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##;
SET @weeks_ago = (CASE WHEN @weeks_ago >0 THEN -@weeks_ago ELSE @weeks_ago END);

DECLARE @target_week DATETIME;
SET @target_week = DATEADD(WEEK, @weeks_ago, CURRENT_TIMESTAMP);

-- POST-RELATED VARIABLES

DECLARE @downvote TINYINT;
SET @downvote = (SELECT Id FROM VoteTypes WHERE Name LIKE 'Down%'); --3

DECLARE @question_post TINYINT;
SET @question_post = (SELECT Id FROM PostTypes WHERE Name = 'Question'); --1

DECLARE @closed_post TINYINT;
SET @closed_post = (SELECT Id FROM PostHistoryTypes WHERE Name = 'Post Closed'); --10

DECLARE @deleted_post TINYINT;
SET @closed_post = (SELECT Id FROM PostHistoryTypes WHERE Name = 'Post Deleted'); --12

WITH cte_downvoted_posts AS
(
  SELECT Votes.PostId AS dvote
  FROM Votes
  INNER JOIN Posts ON Votes.PostId = Posts.Id
  WHERE VoteTypeId = @downvote
)


SELECT 
    Posts.Id AS [Post Link]               -- magic column
  , Posts.OwnerUserId AS [User Link]      -- magic column
  , Posts.CreationDate AS [Creation Date]
  , Posts.ClosedDate AS [Closed Date]

FROM Posts
INNER JOIN cte_downvoted_posts
    ON Posts.Id = cte_downvoted_posts.dvote
INNER JOIN PostHistory
    ON Posts.Id = PostHistory.PostId

WHERE Posts.CreationDate <= @today
  AND Posts.CreationDate > @target_week
  AND Posts.PostTypeId = @question_post
  AND Posts.ClosedDate IS NOT NULL

GROUP BY
    Posts.Id
  , Posts.OwnerUserId
  , Posts.CreationDate
  , Posts.ClosedDate

ORDER BY
    Posts.CreationDate DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results