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
  ROW_NUMBER() OVER(ORDER BY COUNT(1)  DESC) rn
  ,t.tagname
  ,COUNT(1) AS post_count
  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 t.tagname
)
SELECT gd.thedate
,s.tagname
,s.post_count
,SUM(s.post_count) OVER (PARTITION BY s.tagname ORDER BY gd.thedate) AS running_total
FROM getdates gd
INNER JOIN post_tags pp
ON pp.rn <= 5
LEFT JOIN 
 (SELECT CAST(p.creationdate AS DATE) AS post_date
           ,t.tagname
           ,COUNT(1) AS post_count
    FROM tags t
    JOIN posttags pt ON t.id = pt.tagid
    JOIN posts p ON pt.postid = p.id
    JOIN post_tags ppt ON t.tagname = ppt.tagname AND ppt.rn <= 5
    
    WHERE YEAR(p.creationdate) = YEAR(SYSDATETIME())
    GROUP BY CAST(p.creationdate AS DATE), t.tagname) s
ON gd.thedate = s.post_date AND s.tagname = pp.tagname
ORDER BY s.tagname, gd.thedate

fork query

edited mar 20

created mar 10 anonymous