Average response time by tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

The average response time for most popular tags (with at least one vote)

Stack Overflow

Q&A for programmers

-- Average response time by tags
-- The average response time for most popular tags (with at least one vote)

SELECT  TagName, p.*
FROM    Tags t
CROSS APPLY
        (
        SELECT  COUNT(*) AS [Questions],
                COUNT(taf) AS [Answered with Upvote],
                ROUND(AVG(taf / 1.0), 0) [Average URT (Upvoted Response Time)],
                ROUND(MIN(taf / 1.0), 0) [Min URT],
                ROUND(MAX(CASE mf WHEN 1 THEN taf END / 1.0), 0) AS [Median URT],
                ROUND(AVG(CASE mf WHEN 1 THEN taf END / 1.0), 0) AS [Average URT (Below Median)],
                COUNT(taa) AS [Accepted],
                ROUND(AVG(taa / 1.0), 0) AS [Average ART (Accepted Response Time)],
                ROUND(MIN(taa / 1.0), 0) AS [Min ART],
                ROUND(MAX(CASE ma WHEN 1 THEN taa END / 1.0), 0) AS [Median ART],
                ROUND(AVG(CASE ma WHEN 1 THEN taa END / 1.0), 0) AS [Average ART (Below Median)]
        FROM    (
                SELECT  DATEDIFF(second, p.CreationDate, af.CreationDate) AS taf,
                        DATEDIFF(second, p.CreationDate, aa.CreationDate) AS taa,
                        NTILE(2) OVER (ORDER BY CASE WHEN af.CreationDate IS NULL THEN 1 ELSE 0 END, DATEDIFF(second, p.CreationDate, af.CreationDate)) AS mf,
                        NTILE(2) OVER (ORDER BY CASE WHEN aa.CreationDate IS NULL THEN 1 ELSE 0 END, DATEDIFF(second, p.CreationDate, aa.CreationDate)) AS ma
                FROM    PostTags pt
                JOIN    Posts p
                ON      p.Id = pt.PostId
                        AND p.PostTypeId = 1
                LEFT JOIN
                        Posts aa
                ON      aa.Id = p.AcceptedAnswerId
                OUTER APPLY
                        (
                        SELECT  MIN(CreationDate) AS CreationDate
                        FROM    Posts pa
                        WHERE   pa.ParentId = p.Id
                                AND Score >= 1
                        ) af
                WHERE   pt.TagId = t.Id
                        AND p.CreationDate >= '2010-01-01'
                        AND NOT EXISTS
                        (
                        SELECT  NULL
                        FROM    Posts pb
                        WHERE   pb.ParentId = p.Id
                                AND pb.CreationDate <= p.CreationDate
                        )
                ) q
        ) p
WHERE   Count >= 1000
ORDER BY
        [Average URT (Below Median)]

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached