0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

-- Calculate body length vs response time for posts with comments
-- Between '2021-05-03' and '2021-05-10'

WITH post_comments AS
(
    SELECT
        postid,
        MIN(creationdate) AS first_comment
    FROM comments
    GROUP BY postid
)
SELECT
    posts.id AS post_id,
    LEN(posts.body) AS body_length,
    DATEDIFF(minute, posts.CreationDate, post_comments.first_comment) AS response_time_in_minutes
FROM posts
INNER JOIN post_comments
    ON posts.id = post_comments.postid
WHERE
    posts.CreationDate >= '2021-05-03'
    AND posts.CreationDate <= '2021-05-10'
    AND (posts.ClosedDate IS NULL OR posts.ClosedDate <= '2021-05-10')
    AND posts.posttypeid = 1;  -- Ensure we are only considering questions (posttypeid = 1)

fork query

edited may 21

created may 20 anonymous