0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

WITH TopTags AS (
    SELECT 
    TOP 5 pt.TagId,
    COUNT(pt.PostId) AS PostCount
    FROM PostTags pt 
    JOIN Posts p 
    ON pt.PostId = p.Id
    AND p.CreationDate >= DATETRUNC(year, GETDATE())
    WHERE p.Id >= 77734096
    GROUP BY pt.TagId
    ORDER BY PostCount DESC
    ),
DayPostsCounts AS(
    SELECT CONVERT(DATE, p.CreationDate) AS CDate,
    t.TagName, COUNT(pt.PostId) CCnt
    FROM Tags t
    JOIN TopTags tt
    ON tt.TagId = t.Id
    JOIN PostTags pt
    ON t.Id = pt.TagId
    JOIN Posts p
    ON pt.PostId = p.Id
    AND p.CreationDate >= DATETRUNC(year, GETDATE())
    WHERE p.Id >= 77734096
    GROUP BY CONVERT(DATE, p.CreationDate), t.TagName
    )
SELECT 
CDate, 
Tagname, 
SUM(CCnt) OVER (PARTITION BY TagName ORDER BY CDate) AS CCounts
FROM DayPostsCounts
ORDER BY TagName, CDate

view current version

edited dec 3

created dec 3 Janis123