1st questions with answers within X hours

0

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;

SELECT TOP 100
    qUser.Id AS [User Link]
  , qUser.Reputation AS [Q OP Rep]
  , q.Id AS [Post Link]
  --, q.Tags
  , MIN(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 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 messy migrations.
  AND DATEDIFF(HOUR, q.CreationDate, a.CreationDate) >= 0

GROUP BY
    qUser.Id
  , qUser.Reputation
  , q.Id
  --, q.Tags
  , q.CreationDate
  , aUser.Id
  , aUser.Reputation
  , a.Id
  , a.CreationDate

ORDER BY qUser.Id, q.CreationDate DESC, aUser.Reputation DESC;

Enter Parameters

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