0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

-- Calculate number of questions per tag per year and total number of tags per year
WITH QuestionsPerTag AS (
    SELECT
        YEAR(p.CreationDate) AS Year,
        t.TagName,
        COUNT(*) AS QuestionsCount
    FROM
        Posts p
    JOIN
        PostTags pt ON p.Id = pt.PostId
    JOIN
        Tags t ON pt.TagId = t.Id
    WHERE
        p.PostTypeId = 1  -- Filter to include only questions
    GROUP BY
        YEAR(p.CreationDate),
        t.TagName
),
TagsPerYear AS (
    SELECT
        YEAR(p.CreationDate) AS Year,
        COUNT(DISTINCT t.TagName) AS TotalTags
    FROM
        Posts p
    JOIN
        PostTags pt ON p.Id = pt.PostId
    JOIN
        Tags t ON pt.TagId = t.Id
    WHERE
        p.PostTypeId = 1  -- Filter to include only questions
    GROUP BY
        YEAR(p.CreationDate)
)
SELECT
    q.Year,
    q.TagName,
    q.QuestionsCount,
    t.TotalTags
FROM
    QuestionsPerTag q
JOIN
    TagsPerYear t ON q.Year = t.Year
ORDER BY
    q.Year,
    q.TagName;

fork query

edited sep 12

created may 8 18 anonymous