1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

/*
 * 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 (
  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 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
  
ORDER BY 
    q.CreationDate DESC
  , aUser.Reputation DESC;

fork query

edited apr 20 15

created apr 18 15 Phrancis