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

WITH getdates AS
(
    SELECT CAST('2023-01-01' AS DATE) AS thedate
    UNION ALL
    SELECT DATEADD(DAY, 1, thedate) from getdates
    WHERE thedate < GETDATE()
)
, post_tags AS
(
    SELECT CAST(p.creationdate AS DATE) AS post_date
           ,t.tagname
           ,COUNT(*) AS post_count
           ,ROW_NUMBER() OVER (PARTITION BY CAST(p.creationdate AS DATE) ORDER BY COUNT(*) DESC) AS rn
    FROM tags t
    JOIN posttags pt ON t.id = pt.tagid
    JOIN posts p ON pt.postid = p.id
    WHERE YEAR(p.creationdate) = YEAR(SYSDATETIME())
    GROUP BY CAST(p.creationdate AS DATE), t.tagname
)
SELECT ppt.post_date
  ,ppt.tagname
  ,post_count
  ,SUM(ppt.post_count) OVER(ORDER BY ppt.post_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM post_tags ppt
GROUP BY ppt.post_date, ppt.tagname, ppt.post_count
ORDER BY ppt.post_date, ppt.post_count DESC;

fork query

edited mar 20

created mar 10 anonymous