Calculate index position using sub queries on group by using date

Question

select user_id as sponsor_id,sum(points),created_at 
from points_history 
where created_at between '2014/08/12' and '2015/08/12' and transaction_type="debit" 
group by user_id,DATE_FORMAT(created_at,"%d %M %Y") 
order by DATE_FORMAT(created_at,"%d %M %Y"),sum(points) desc



sponsor_id      sum(points)     created_at    
1               30             2014-12-08 10:54:59
2               25             2014-12-09 05:43:11
3               20             2014-12-09 06:58:40
1               5              2014-12-09 05:56:12
1               34             2014-08-23 10:42:32

here I want to calculate rank of particular sponsor using sponsor_id on daily basis .. I want to build a query that can return me something like as displayed below:

sponsor_id      rank    created_at      
1               1               2014-12-08 10:54:59
1               3               2014-12-09 05:56:12
1               1               2014-08-23 10:42:32

I think I can use sub query like

select * 
from (select user_id as sponsor_id,sum(points),created_at 
      from points_history 
      where created_at between '2014/08/12' and '2015/08/12' and transaction_type="debit" 
      group by user_id,DATE_FORMAT(created_at,"%d %M %Y") 
      order by DATE_FORMAT(created_at,"%d %M %Y"),sum(points) desc
) as t 
where t.sponsor_id = 1

but how to calulate rank here.

Answer 1

Try this:

SELECT sponsor_id, points, created_at, 
       IF(@dte=@dte:=DATE(created_at), @rank:=@rank+1, @rank:=1) AS rank
FROM (SELECT user_id AS sponsor_id, SUM(points) points, created_at 
      FROM points_history 
      WHERE created_at BETWEEN '2014-08-12' AND '2015-08-12' AND 
            transaction_type = "debit" 
      GROUP BY user_id, DATE_FORMAT(created_at,"%d %M %Y") 
      ORDER BY DATE(created_at), SUM(points) DESC
     ) AS A, (SELECT @rank:=0, @dte:='') AS B 
ORDER BY DATE(created_at), points DESC;