MySQL: Average rating per item, using latest rating for user -

i have items_ratings table follows:

items_ratings +----+--------+---------+---------+---------------------+ | id | rating | user_id | item_id | created (datetime)  | +----+--------+---------+---------+---------------------+ | 1  | 20     | 1       | 12      | 2017-07-12 14:00:04 | | 2  | 80     | 2       | 12      | 2017-07-12 15:32:12 | | 3  | 50     | 1       | 15      | 2017-08-01 11:14:04 | | 4  | 90     | 1       | 12      | 2017-08-02 19:23:19 | | 5  | 60     | 2       | 15      | 2017-08-05 19:23:19 | +----+--------+---------+---------+---------------------+ 

i need retrieve average value per item_id, using each user's recent rating.

the following gives me average rating each item:

select avg(rating) items_ratings group item_id 

i have identified following query gives me recent row each user_id, item_id.

select max(created), user_id, item_id items_ratings group user_id, item_id; 

i unsure of how should combine these queries yield desired result.

you use select join table max created user_id

  select item_id, avg( rating) (       select * items_ratings         inner join (       select max(created) t_created, user_id       items_ratings       group user_id         ) t on  t.user_id = a.user_id , t.t_created = a.created        ) t1   group item_id 

the inner select max created user_id, other rows macht , outer buil avg on group item_id

and new condition on item_id use

   select item_id, avg( rating) (       select * items_ratings         inner join (       select max(created) t_created, user_id, item_id       items_ratings       group user_id, item_id         ) t on  t.user_id = a.user_id ,  t.t_created = a.created  ,  t.item_id = a.item_id       ) t1   group item_id