I have a table
users and other table
user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG
user_score by only considering latest 10 records of
user_scores table. I have tried following two queries but not successful with these.
UPDATE user SET user.user_score = ( SELECT AVG(score) as avg_score FROM ( SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)
I am facing error
Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query
- Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.