DECLARE @CurrentYear INT = YEAR(GETDATE()); WITH TopTags...

0

Please login or register to vote for this query.

(click on this box to dismiss)

English Language Learners

Q&A for speakers of other languages learning English

DECLARE @CurrentYear INT = YEAR(GETDATE());

WITH TopTags AS (
    SELECT TOP 5
        t.TagName,
        COUNT(*) AS PostCount
    FROM 
        Posts p
    JOIN 
        PostTags pt ON p.Id = pt.PostId
    JOIN 
        Tags t ON t.Id = pt.TagId
    WHERE 
        YEAR(p.CreationDate) = @CurrentYear
    GROUP BY 
        t.TagName
    ORDER BY 
        PostCount DESC
),
DailyPosts AS (
    SELECT 
        CAST(p.CreationDate AS DATE) AS Date,
        t.TagName,
        COUNT(*) AS DailyPostCount
    FROM 
        Posts p
    JOIN 
        PostTags pt ON p.Id = pt.PostId
    JOIN 
        Tags t ON t.Id = pt.TagId
    WHERE 
        YEAR(p.CreationDate) = @CurrentYear
        AND t.TagName IN (SELECT TagName FROM TopTags)
    GROUP BY 
        CAST(p.CreationDate AS DATE), t.TagName
)
SELECT 
    dp.Date,
    dp.TagName,
    SUM(dp.DailyPostCount) OVER (PARTITION BY dp.TagName ORDER BY dp.Date) AS CumulativePostCount
FROM 
    DailyPosts dp
ORDER BY 
    dp.TagName, dp.Date;

Enter Parameters

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