1st questions with answers within X hours


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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: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 (
    , 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.Tags
  , 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 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 messy migrations.
  AND DATEDIFF(HOUR, q.CreationDate, a.CreationDate) >= 0
    q.CreationDate DESC
  , aUser.Reputation DESC;

Enter Parameters

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