Player ranking by categories over months

Question

Imagine the following Player table, with the fields:

PlayerId, Date, Kills and Gold

I need to get the player position by category (kills or gold) over months. This is the SELECT:

SET @rownumber := 0;

SELECT date, rank, kills FROM (
    SELECT pla.event_date, @rownumber := @rownumber + 1 AS rank, 
    pla.kills, pla.player_id
    FROM player AS pla
    INNER JOIN ...
    WHERE.pla.event_date >= '2017-09-01' AND pla.event_date <= '2017-12-31'
    ORDER BY pla.kills DESC
) AS result WHERE player_id = 651894

It works just fine when I filter it by one month. The problem is the @rownumber variable always increment +1 in the next month, which does not bring the right result.

I don't mean to make it a function to iterate over months from backend. How can I do it?

Answer 1

You need another user variable to tell you when it's in a new month.

SET @rownumber := 0;
SET @month := 0;

SELECT pla.event_date, 
  @rownumber := IF(@month=MONTH(pla.event_date), @rownumber + 1, 1) AS rank, 
  @month := MONTH(pla.event_date) AS month,
  pla.kills, pla.player_id
FROM player AS pla
INNER JOIN ...
WHERE pla.event_date >= '2017-09-01' AND pla.event_date <= '2017-12-31'
ORDER BY MONTH(pla.event_date), pla.kills DESC