I have a table storing transactional data for users. To find a users rating you take the average score of the last 10 entries for that user. Is there a way to get that with SQL?
I need to be able to work it out for a single user given their ID. And to get a list of all users ordered by their score.
Currently I'm working it out outside MySQL and storing it in another col for each user so I can ORDER_BY that.
# returns average for all transactions.
SELECT user_id, AVG(score) FROM transactions WHERE user_id = 1
# returns scores for last 10 transactions.
SELECT user_id, score FROM transactions WHERE user_id = 1 ORDER_BY date DESC LIMIT 10