How to calculate user activity over a period of time according to data from the database?

Question

PHP

Hello!
My site has a system of levels for users, which they can increase through activity: posting posts, topics, ratings, etc.
I want to count the most active users for the week - those whose experience has grown the most in the last 7 days.
Once a day, for each user, data about his experience is entered into the user_exp table, which looks like:
user_id | data | exp
...
1 | 03-03-2016| 1238
1 | 02-03-2016| 1218
2 | 03-03-2016| 552
2 | 02-03-2016| 523
...
i.e. the table has information about the user experience for each day
Question:How can I calculate for which user how much experience has grown over a period of time (a week) and get the user IDs with the maximum increase?
Why do I record experience data for each day, and not 2 times a week (at the beginning and at the end, to compare)? Because I want the user to be able to go to the page and see the current TOP: who scored how much in the last day, in the 2nd day, etc.
ps I would also listen to suggestions regarding the structure of the table, maybe I'm doing something wrong and it can be simpler...

Answer 1

select curr.user_id, (curr.exp - coalesce(lastr.exp, 0)) as diff
from user_exp curr
left join user_exp lastr ON curr.user_id = lastr.user_id and lastr.date = CURRENT_DATE - interval '1week'
where  curr.date = CURRENT_DATE
order by diff desc

According to the index by user_id & date, the join will clearly work, the index by date for the initial table. But sorting all users by a calculated field can be painful.