MySQL - Rank per month across several months
<p>I'm using MySQL database. I'm looking to generate the rank of customers month by month for the last 6 months.</p>

<p>I just got the following query to work to determine the rank of a customer in a monthly poll. This reports the rank correctly only if the date range in one month.</p>

<pre><code>    select 
        t1.*, 
        @rownum := @rownum + 1 AS RANK
    from    
        (
            select 
                date_format(EVE_DATE,'%Y-%m') as MON_DATE,
                CUST,
                SUM(POLL) as SCORE
            from
                TABLE
            where 
                EVE_DATE &gt;= '2016-01-01' and EVE_DATE &lt;= '2016-01-31'
            group by
                MON_DATE,
                CUST
            order by 
                SCORE desc
        )t1,
(SELECT @rownum := 0) r 
order by
        RANK DESC
</code></pre>

<p>The problem I have is, if I were to change the date range to span over multiple months, then the rank shown isn't right. I've dug a bit deeper &amp; realize that, the problem is due to the fact that when the number of days span across months, every customer gets listed as many times as the number of months in question. Thereby, number of rows in the output is <code>number_of_customers * number of months</code> which means the rank per month is no longer a meaningful value.</p>

<p>For example, if there are 100 customers &amp; if I were to calculate the rank for one month, the maximum rank I can have is 100 which is correct. However, if I considered 2 months, the rank can range from 1 to 200 which is incorrect. This is because there are only 100 customers, but, are appearing twice due to 2 months being the consideration.</p>

<p>How could I correct the below query to show me rank per month correctly?</p>

<pre><code>select 
    t2.*
from
(
        select 
            t1.*, 
            @rownum := @rownum + 1 AS RANK
        from    
            (
                select 
                    date_format(EVE_DATE,'%Y-%m') as MON_DATE,
                    CUST,
                    SUM(POLL) as SCORE
                from
                    TABLE
                where 
                    EVE_DATE &gt;= (curdate() - INTERVAL 3 MONTH)
                group by
                    MON_DATE,
                    CUST
                order by 
                    SCORE desc
            )t1,
    (SELECT @rownum := 0) r 
    order by
            RANK DESC
)t2
where
    t2.CUST= 'customerA'
order by
    t2.MON_DATE desc
</code></pre>

<p>I'd appreciate any help here to get me going please.        </p>