Average rep gained on answers by their delay after question creation and chronological order

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

WITH Raw AS (
  SELECT
    MIN(Posts.ParentId) AS QId,
    Posts.Id AS Id,
    MIN(Posts.CreationDate) AS CreationDate,
    SUM(
      CASE
        WHEN Votes.VoteTypeId = 1 THEN 15
        WHEN Votes.VoteTypeId = 2 THEN 10
        WHEN Votes.VoteTypeId = 3 THEN -2
        ELSE 0
      END
    ) AS Rep
  FROM Posts
  LEFT JOIN Votes ON
    Votes.VoteTypeId BETWEEN 1 AND 3
    AND
    Votes.PostId = Posts.Id
    AND
    DATEDIFF(day, Posts.CreationDate, Votes.CreationDate)
      BETWEEN 0 AND ##MaxVoteDelayDay:int?30##
  WHERE
    Posts.PostTypeId = 2
  GROUP BY Posts.Id
),
Raw2 AS (
  SELECT
    Raw.Id,
    Raw.Rep,
    DATEDIFF(hour, Q.CreationDate, Raw.CreationDate) AS HourDelay,
    RANK() OVER (PARTITION BY Raw.QId ORDER BY Raw.Id) AS N
  FROM Raw, Posts Q
  WHERE
    Raw.QId = Q.Id
    AND
    DATEDIFF(hour, Q.CreationDate, Raw.CreationDate)
      BETWEEN 0 AND ##MaxAnswerDelayHour:int?168##
)
SELECT
  HourDelay,
  CAST(N AS VARCHAR(MAX)) AS NthAnswer,
  AVG(1.0 * Rep) AS AvgRep
FROM Raw2
WHERE N <= ##MaxAnswerNo:int?5##
GROUP BY HourDelay, N
ORDER BY HourDelay, N

Enter Parameters

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