Fastest Guns in the West

1

Please login or register to vote for this query.

(click on this box to dismiss)

Users with fastest response time

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

-- p1: Answers posted within x minutes
-- p2: At least x qualifying answers
-- p3: Number of results

WITH cte AS (
    SELECT a.OwnerUserId
         , COUNT(*) AS QualifyingAnswers
         , AVG(Delta) AS AvgResponseTime
    FROM Posts AS a
    INNER JOIN Posts AS q ON a.ParentId = q.Id
    CROSS APPLY (
        SELECT DATEDIFF(SECOND, q.CreationDate, a.CreationDate)
    ) AS x(Delta)
    WHERE a.PostTypeId = 2               -- is answer
    AND a.OwnerUserId IS NOT NULL        -- answer not owned by anonymous
    AND (
        q.OwnerUserId IS NULL OR
        q.OwnerUserId <> a.OwnerUserId   -- question not asked by self
    )
    AND a.CreationDate >= q.CreationDate -- answer not merged with newer question
    AND x.Delta <= ##p1:int?30## * 60    -- answer within threshold
    GROUP BY a.OwnerUserId
    HAVING COUNT(*) > ##p2:int?200##
)
SELECT DENSE_RANK() OVER (ORDER BY AvgResponseTime) AS [Rank]
     , OwnerUserId AS [User Link]
     , QualifyingAnswers [Qualifying Answers]
     , CAST(DATEADD(SECOND, AvgResponseTime, 0) AS TIME) AS [Average Response Time]
FROM cte
ORDER BY AvgResponseTime
OFFSET 0 ROWS FETCH NEXT ##p3:int?1000## ROWS ONLY

Enter Parameters

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