SELECT * FROM (SELECT YEAR(Posts.CreationDate) as C...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

SELECT * FROM

 

  (SELECT

 

    YEAR(Posts.CreationDate) as CreationYear,

    MONTH(Posts.CreationDate) as CreationMonth,

    TagName,

    COUNT(Posts.Id) as PostCount,

    RANK() OVER (PARTITION BY YEAR(Posts.CreationDate), MONTH(Posts.CreationDate) ORDER BY COUNT(PostId) DESC) AS Rank

    --RANK() OVER (PARTITION BY YEAR(Posts.CreationDate) ORDER BY COUNT(PostId) DESC) AS Rank

 

  FROM

 

    Tags, PostTags, Posts, Users

 

  WHERE

 

    Tags.Id = PostTags.TagId and

    Posts.Id = PostId and

    Posts.OwnerUserId = Users.Id

    and (LOWER(Users.Location) like '%brazil' or LOWER(Users.Location) like '%brasil')

 

  GROUP BY YEAR(Posts.CreationDate), MONTH(Posts.CreationDate), TagName)

  AS TagPostRankings

 

WHERE

  --Rank <= 10 AND

  CreationYear = 2023 AND 
  CreationMonth = 12
  

 

--ORDER BY CreationYear, CreationMonth, Rank

ORDER BY CreationYear, CreationMonth, Rank

Enter Parameters

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