0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for power users of web applications

WITH TopTags AS (
  SELECT 
    top 5 pt.tagId, 
    t.tagname, 
    COUNT(*) AS PostCount 
  FROM 
    posttags pt 
    left join tags t on t.id = pt.tagid 
    JOIN posts p ON pt.PostId = p.Id 
  WHERE 
    year(p.creationdate) >= 2024 
    and p.posttypeid = 1  -- refers only to questions, as including more retuns in timeouts
  GROUP BY 
    pt.tagId, 
    t.tagname 
  ORDER BY 
    PostCount DESC
), 
CumulativePosts AS (
  SELECT 
    CAST(p.creationdate AS DATE) AS Date, 
    tt.tagname, 
    COUNT(p.Id) OVER (
      PARTITION BY tt.tagname 
      ORDER BY 
        CAST(p.creationdate AS DATE) ROWS BETWEEN UNBOUNDED PRECEDING 
        AND CURRENT ROW
    ) AS CumulativeCount 
  FROM 
    posts p 
    JOIN posttags pt ON p.Id = pt.PostId 
    JOIN TopTags tt ON pt.tagId = tt.tagId 
  WHERE 
    year(p.creationdate) >= 2024 
    and p.posttypeid = 1 -- refers only to questions, as including more retuns in timeouts
) 
SELECT 
  FORMAT(date, 'yyyy-MM-dd') as Date, 
  tagname, 
  sum(CumulativeCount) AS CumulativePosts 
FROM 
  CumulativePosts 
group by 
  Date, 
  tagname 
ORDER BY 
  Date, 
  tagname;

view current version

edited dec 4

created dec 4 Janis123