0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for legal professionals, students, and others with experience or interest in law

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;

fork query

created may 20 anonymous