Q&A about the site for science fiction and fantasy enthusiasts
WITH post_comments AS ( SELECT postid, MIN(creationdate) AS first_comment FROM comments GROUP BY postid ), question_tag_counts AS ( SELECT p.id AS post_id, COUNT(pt.tagid) AS tag_count FROM posts p INNER JOIN posttags pt ON p.id = pt.postid WHERE p.CreationDate >= '2021-05-03' AND p.CreationDate <= '2021-05-10' AND (p.ClosedDate IS NULL OR p.ClosedDate <= '2021-05-10') AND p.posttypeid = 1 -- Ensure we are only considering questions (posttypeid = 1) GROUP BY p.id ) SELECT p.id AS post_id, qt.tag_count, DATEDIFF(hour, p.CreationDate, pc.first_comment) AS response_time_in_hours FROM question_tag_counts qt INNER JOIN post_comments pc ON qt.post_id = pc.postid INNER JOIN posts p ON qt.post_id = p.id;