Popular Tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

WITH post_comments AS
(
    SELECT
        postid,
        MIN(creationdate) AS first_comment
    FROM comments
    GROUP BY postid
),
popular_tags_check AS
(
    SELECT
        id AS post_id,
        CASE
            WHEN tags LIKE '%<javascript>%' THEN 1
            WHEN tags LIKE '%<python>%' THEN 1
            WHEN tags LIKE '%<java>%' THEN 1
            WHEN tags LIKE '%<c#>%' THEN 1
            WHEN tags LIKE '%<php>%' THEN 1
            WHEN tags LIKE '%<html>%' THEN 1
            WHEN tags LIKE '%<android>%' THEN 1
            ELSE 0
        END AS contains_popular_tag
    FROM posts
    WHERE
        CreationDate >= '2021-05-03'
        AND CreationDate <= '2021-05-10'
        AND (ClosedDate IS NULL OR ClosedDate <= '2021-05-10')
        AND posttypeid = 1  -- Ensure we are only considering questions (posttypeid = 1)
)
SELECT
    posts.id AS post_id,
    popular_tags_check.contains_popular_tag,
    DATEDIFF(minute, posts.CreationDate, post_comments.first_comment) AS response_time_in_minutes
FROM popular_tags_check
INNER JOIN post_comments
    ON popular_tags_check.post_id = post_comments.postid
INNER JOIN posts
    ON popular_tags_check.post_id = posts.id;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results