0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH tmp AS (
SELECT
YEAR(p.CreationDate) as YEAR, MONTH(p.CreationDate) as MONTH,
pt.TagId as TagId, COUNT(p.Id) as PostsCount
from Posts p right join (select * from PostTags) pt on p.Id = pt.PostId
group by YEAR(p.CreationDate), MONTH(p.CreationDate), pt.TagId
),
tmp_tags AS (
SELECT * FROM Tags WHERE Id in (SELECT TagId FROM tmp)
)


SELECT tt.TagName as TagName, t.YEAR, t.MONTH
FROM (SELECT TagId, MONTH, YEAR, MAX(PostsCount) as MaxPostsCount FROM tmp GROUP BY TagId, MONTH, YEAR) t
LEFT JOIN tmp_tags tt ON tt.Id = t.TagId

fork query

edited nov 25

created nov 25 jon.doe37500