Query issue ranking system MYSQL

Question

I have a ranking system where I save the users rank and points for every game day.

Now my problem is that I want to fetch the number of rank-positions that a user have climbed since last day. So in this example the user_id = 1 has dropped 3 positions since yesterday. My current query is giving me kind of what I want, but with some extra calculation that I want to remove. So my question is how do I calculate the difference in rank for every user (between today and yesterday)? SQL FIDDLE

Answer 1

SELECT current.user_id,(last.rank -current.rank)
FROM ranking as current
LEFT JOIN ranking as last ON 
last.user_id = current.user_id
WHERE current.rank_date = (SELECT max(rank_date) FROM ranking)
and
last.rank_date = (SELECT max(rank_date) FROM ranking 
 where rank_date <  (SELECT max(rank_date) FROM ranking)               
                 )