Q&A for user experience researchers and experts
WITH post_comments AS ( SELECT postid, MIN(creationdate) AS first_comment FROM comments GROUP BY postid ), tagged_posts AS ( SELECT p.id AS post_id FROM posts p INNER JOIN posttags pt ON p.id = pt.postid INNER JOIN tags t ON pt.tagid = t.id 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 AND t.tagname IN ('python', 'javascript', 'java', 'html', 'android', 'c#', 'reactjs', 'php', 'node.js', 'c++') ), untagged_posts AS ( SELECT id AS post_id FROM posts WHERE id NOT IN (SELECT post_id FROM tagged_posts) AND CreationDate >= '2021-05-03' AND CreationDate <= '2021-05-10' AND (ClosedDate IS NULL OR ClosedDate <= '2021-05-10') AND posttypeid = 1 ) SELECT p.id AS post_id, CASE WHEN tp.post_id IS NOT NULL THEN 'Tagged' ELSE 'Untagged' END AS post_category, DATEDIFF(hour, p.CreationDate, pc.first_comment) AS response_time_in_hours FROM posts p INNER JOIN post_comments pc ON p.id = pc.postid LEFT JOIN tagged_posts tp ON p.id = tp.post_id LEFT JOIN untagged_posts up ON p.id = up.post_id;