0

Please login or register to vote for this query.

(click on this box to dismiss)

Determines what are the hot tags for a give week of the year --overalRankMin is the minimum a overall rank must be: The smaller the number the more popular the tag must be. Zero=All

Q&A for programmers

Declare @Year int ,
        @week int ,
        @overalRankMin int

set @year = ##year:int##
set @week = ##week:int##
set @overalRankMin = ##overalRankMin:int##



;With weekRank as 
(SELECT  
         t.id,
         COUNT(p.id) k 
FROM   posts p 
       INNER JOIN posttags pt 
               ON p.id = pt.postid 
       INNER JOIN tags t 
               ON pt.tagid = t.id 
WHERE
       Year(p.creationdate) = @Year 
       AND Datepart(week, p.creationdate) = @Week  

GROUP  BY
     t.id)

, combined As
(
SELECT 
    rank() over (order by t.count desc) overall_rank,
    rank() over (order by wr.k desc) week_Rank,
    t.count overallCount,
    wr.k weekCount,
    tagname
FROM 
     tags t
      INNER JOIN weekrank wr
      ON t.id = wr.id 
           
)
SELECT tagName,
       week_Rank ,
       overall_rank,
       weekCount,
       overallCount,       
       overall_rank - week_Rank  as difference
FROM combined
WHERE (overall_rank <=   @overalRankMin 
       or @overalRankMin = 0)

ORDER BY 
       overall_rank - week_Rank desc

fork query

edited jul 31

created jul 31 conradfrix