Age-Score correlation (first answers only, logarithmic binning)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

-- this script will bin the collective score of answers as a function
--  of age between asking the Q and posting the A. 

WITH Question AS (
  SELECT
    Question.Id,
    Question.CreationDate,
    Question.OwnerUserId,
    MIN(Answer.CreationDate) AS FirstAnswerDate
  FROM Posts AS Question
  JOIN Posts AS Answer ON Answer.ParentId = Question.Id
  WHERE Question.ClosedDate IS NULL
  GROUP BY Question.Id, Question.CreationDate, Question.OwnerUserId
)
SELECT
  ROUND(LOG(DATEDIFF(ss, Question.CreationDate, Answer.CreationDate), 10), 1) AS [Log Delta-T],
  ROUND(AVG(CONVERT(FLOAT, Answer.Score)), 2) AS [Average score],
  ROUND(STDEVP(CONVERT(FLOAT, Answer.Score)), 2) AS [Standard deviation],
  COUNT(*) AS [Answers]
FROM Posts AS Answer
JOIN Question ON Answer.ParentId = Question.Id
WHERE Answer.CreationDate = Question.FirstAnswerDate
  AND Answer.OwnerUserId != Question.OwnerUserId
  AND DATEDIFF(ss, Question.CreationDate, Answer.CreationDate) >= 1
GROUP BY ROUND(LOG(DATEDIFF(ss, Question.CreationDate, Answer.CreationDate), 10), 1)
ORDER BY [Log Delta-T] ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results