WITH tags_this_year AS( SELECT t.Id, t.TagName, COUNT(p.I...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

WITH tags_this_year AS(
SELECT t.Id,
       t.TagName,
       COUNT(p.Id) post_cnt,
       DENSE_RANK() OVER (
       ORDER BY COUNT(p.Id) DESC ) tag_posts_rank
FROM Tags t
JOIN PostTags pt ON t.Id = pt.TagId
JOIN Posts p ON pt.PostId = p.Id AND p.CreationDate >= '2021-01-01'
GROUP BY t.Id, t.TagName
)
SELECT '2021-02-01', tt.TagName, COUNT(pp.Id)
FROM tags_this_year tt
JOIN PostTags ptt ON tt.Id = ptt.TagId
JOIN Posts pp ON ptt.PostId = pp.Id AND pp.CreationDate >= '2021-01-01' AND pp.CreationDate < '2021-02-02'
WHERE tt.tag_posts_rank <= 5
GROUP BY tt.Id, tt.TagName;

Enter Parameters

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