0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and students of the Korean language

DECLARE @MostRecentDate Date
SET @MostRecentDate = (SELECT MAX(CreationDate) FROM Posts WHERE PostTypeId = 1);

SELECT
    Tags.TagName,
    CASE 
        WHEN Questions.CreationDate >= DATEADD(day, -30, @MostRecentDate) THEN 'Last 30 Days'
        WHEN Questions.CreationDate >= DATEADD(day, -60, @MostRecentDate) THEN '30 to 60 Days Ago'
    END AS TimeFrame,
    COUNT(*) AS QuestionCount
FROM
    Tags
    JOIN PostTags ON Tags.Id = PostTags.TagId
    JOIN Posts AS Questions ON PostTags.PostId = Questions.Id
WHERE
    Questions.PostTypeId = 1 -- Questions only
    AND Questions.CreationDate >= DATEADD(day, -60, GETDATE()) -- Questions from the last 60 days
GROUP BY
    Tags.TagName,
    CASE 
        WHEN Questions.CreationDate >= DATEADD(day, -30, @MostRecentDate) THEN 'Last 30 Days'
        WHEN Questions.CreationDate >= DATEADD(day, -60, @MostRecentDate) THEN '30 to 60 Days Ago'
    END
ORDER BY
    Tags.TagName, TimeFrame DESC, QuestionCount DESC;

fork query

edited feb 21

created feb 21 anonymous