I recently challenged @Hosch520 with writing a query as such:
Find first questions with answers posted within 24 hours
And he did great. I figured I would have a go at it too. Turns out I learned valuable things about data subsets, so here I am seeking review.
I did not specify whether to return only the first answer within 24 hours, or all answers within 24 hours, so we went about it a bit differently. The remarks at the top of my query give more details.
In any case, mine returns all answers posted to a new user's first question within 24 hours.
/* * Author: @Phrancis * Title: 1st questions with answers within X hours * Date: 2015-04-16 * Purpose: * To query a new user's first question under the condition that * the question received one or more answers within a certain number of * hours (default 24), as well as all qualifying answers, and both users' * name and current reputation. It also calculates the interval between * question and answers. */ DECLARE @HoursElapsed AS INT; SET @HoursElapsed = ##NumberOfHours:float?24##; -- NumberOfHours: Hours elapsed between OP 1st question and answers following it "Decimals are allowed" DECLARE @QuestionType INT; DECLARE @AnswerType INT; SET @QuestionType = 1; SET @AnswerType = 2; WITH AllFirstQuestions AS ( SELECT OwnerUserId , Id FROM Posts WHERE CreationDate IN (SELECT MIN(CreationDate) FROM Posts GROUP BY OwnerUserId) AND PostTypeId = @QuestionType ) SELECT qUser.Id AS [User Link] , qUser.Reputation AS [Q OP Rep] , firstQ.Id AS [Post Link] , q.CreationDate AS [Q Date] , CONVERT( DECIMAL(10,2), DATEDIFF(MINUTE, q.CreationDate, a.CreationDate) /60.0 ) AS [Hours Elapsed] , aUser.Id AS [User Link] , aUser.Reputation AS [A OP Rep] , a.Id AS [Post Link] , a.CreationDate AS [A Date] FROM Posts AS q INNER JOIN AllFirstQuestions AS firstQ ON q.Id = firstQ.Id INNER JOIN Posts AS a ON q.Id = a.ParentId AND q.PostTypeId = @QuestionType AND a.PostTypeId = @AnswerType INNER JOIN Users qUser ON q.OwnerUserId = qUser.Id AND q.OwnerUserId IS NOT NULL INNER JOIN Users aUser ON a.OwnerUserId = aUser.Id AND a.OwnerUserId IS NOT NULL WHERE a.CreationDate <= (DATEADD(HOUR, @HoursElapsed, a.CreationDate)) AND DATEDIFF(HOUR, q.CreationDate, a.CreationDate) <= @HoursElapsed /* This condition is needed because some "fluke" questions * have an answer creation time that is before the question's. * Usually due to complicated migrations. */ AND DATEDIFF(HOUR, q.CreationDate, a.CreationDate) >= 0 ORDER BY q.CreationDate DESC , aUser.Reputation DESC;
I'm looking to see if this can be improved. Maybe mine is too much? I personally feel it is reasonable, and it executes pretty fast:
14662 rows returned in 194 ms
I scanned through the output for a while to make sure I did not have multiple first questions by the same user, and AFAIK, I do not. I'm open to any criticism to make this better!