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


Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

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

WITH tags_this_year AS(
       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-02'
WHERE tt.tag_posts_rank <= 5
GROUP BY tt.Id, tt.TagName;

Enter Parameters

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