MySQL tagging system - top keywords per month

Question

I have a "Has-many-through" table keyword_sentence which contains links from the sentences to the keywords.

TABLE `keyword_sentence` {
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sentence_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sentence_id` (`sentence_id`),
  KEY `keyword_id` (`keyword_id`)
)

How do I get the top 5 keywords per week?

I would like to see which keyword_id's are being used each week so I can watch for trending items. I currently have the following query which isn't quite working.

SELECT ks.keyword_id
FROM
  keyword_sentence ks
WHERE ks.keyword_id IN (
  SELECT DISTINCT ks2.keyword_id FROM keyword_sentence ks WHERE
  from_unixtime(ks.created) >= CURRENT_DATE - INTERVAL 2 MONTH
  AND 
  from_unixtime(ks.created) < CURRENT_DATE - INTERVAL 1 MONTH
)
ORDER BY COUNT(*) DESC

Answer 1

Try this query

SELECT *
FROM (
    SELECT *, @rowNo := if(@pv = week, @rowNo+1, 1) as rNo, @pv := week
    FROM (
        SELECT keyword_id, COUNT(*), YEARWEEK(FROM_UNIXTIME(created)) AS week
        FROM keyword_sentence
        WHERE 
            FROM_UNIXTIME(created) >= CURRENT_DATE - INTERVAL 2 MONTH
        AND
            FROM_UNIXTIME(created) < CURRENT_DATE - INTERVAL 1 MONTH
        GROUP BY week, keyword_id
        ORDER BY week, COUNT(*) DESC
    ) temp
    JOIN (
        SELECT @rowNo := 0, @pv := 0
    ) tempValue
) tmp
WHERE
    tmp.rNo < 6

Fiddle

Hope this helps