0

Please login or register to vote for this query.

(click on this box to dismiss)

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

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

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

SELECT  TagName, ResponseTime
FROM    Tags t
CROSS APPLY
        (
        SELECT  AVG(DATEDIFF(second, p.CreationDate, pa.CreationDate) * 1.) AS ResponseTime
        FROM    PostTags pt
        JOIN    Posts p
        ON      p.Id = pt.PostId
                AND p.PostTypeId = 1
        CROSS APPLY
                (
                SELECT  TOP 1 *
                FROM    Posts pa
                WHERE   pa.ParentId = p.Id
                        AND pa.PostTypeId = 2
                        AND Score >= 1
                ORDER BY
                        CreationDate
                ) pa
        WHERE   pt.TagId = t.Id
        ) p
WHERE   Count >= 1000

fork query

created apr 15 11 Quassnoi