0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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-01-02', 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-01-01 00:10:00'
WHERE tt.tag_posts_rank <= 5
GROUP BY tt.Id, tt.TagName;

view current version

edited oct 27

created oct 27 jon.doe37292