0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

WITH post_comments AS
(
    SELECT
        postid,
        MIN(creationdate) AS first_comment_date
    FROM comments
    GROUP BY postid
),
posts_with_first_comment_views AS
(
    SELECT
        p.id AS post_id,
        p.ViewCount,
        pfc.first_comment_date,
        DATEDIFF(minute, p.CreationDate, pfc.first_comment_date) AS response_time_in_minutes
    FROM posts p
    INNER JOIN post_comments pfc
        ON p.id = pfc.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)
)
SELECT
    post_id,
    ViewCount AS views_at_first_comment,
    response_time_in_minutes
FROM posts_with_first_comment_views;

fork query

edited may 21

created may 21 anonymous