1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

with relevant_questions as (
  select Posts.Id, Title, Tags, Year(CreationDate) CreationYear, Month(CreationDate) CreationMonth, Score
  from Posts
  inner join PostTags on Posts.Id = PostTags.PostId
  inner join Tags on Tags.Id = PostTags.TagId
  where 1 = 1
  and Tags.TagName = '##Tag##'
  and PostTypeId = 1
  and Score > 0  
),
ranked_questions as (
  select relevant_questions.*, row_number()
  over (partition by CreationYear, CreationMonth order by Score desc) ScoreRank
  from relevant_questions
)
select Id, Title, Tags, CreationYear, CreationMonth, Score, ScoreRank
from ranked_questions
where ScoreRank <= 5
order by CreationYear, CreationMonth, Score desc

fork query

edited sep 5 20

created jun 18 20 Renato Dinhani