1st questions with answers within X hours


Please login or register to vote for this query.

(click on this box to dismiss)

Code Review

Q&A for peer programmer code reviews

 * 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:int?24##;
-- NumberOfHours: Hours elapsed between OP 1st question and answers following it

DECLARE @QuestionType INT;
SET @QuestionType = 1;
DECLARE @AnswerType INT;
SET @AnswerType = 2;

WITH AllFirstQuestions AS (
    , Id
  FROM Posts
  WHERE CreationDate IN (SELECT MIN(CreationDate) FROM Posts GROUP BY OwnerUserId)
    AND PostTypeId = @QuestionType

    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
    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 q.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
    q.CreationDate DESC
  , aUser.Reputation DESC;

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results