Some fellow code reviewers (hi @Janos!) have been inquiring about a SEDE query to allow to check progress of the Red Shirt "hat" progression.
cast 5 downvotes on posts that are later deleted or closed
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.
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.
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.
/* 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 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 -- QUERY BEGINS 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
Nitpicks are fine! Anything from naming to indentation to inconsistencies, please don't be shy to point out anything at all!
I noticed a lot of nested loops in the execution plan, when it's doing joins. Is there a better way to do this to avoid them?
Is there a way to make this query result set more useful, or user-friendly?
Are my comments appropriate/useful? Should I have fewer, or more of them?