0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

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;

fork query

created may 20 anonymous