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

Question

I have the items_ratings table as 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 to retrieve the average value per item_id, using each user's most recent rating.

The following gives me the average rating for each item:

SELECT AVG(rating) FROM items_ratings
GROUP BY item_id

I have also identified that the following query gives me the most recent row for each user_id, by item_id.

SELECT MAX(created), user_id, item_id FROM items_ratings
GROUP BY user_id, item_id;

I am unsure of how I should combine these queries to yield my desired result.

Answer 1

You could use a select from join table with max created by user_id

  select item_id, avg( rating) from (
      select * from items_ratings  a 
      inner join (
      SELECT MAX(created) t_created, user_id
      FROM items_ratings
      GROUP BY user_id
        ) t on  t.user_id = a.user_id and t.t_created = a.created 
      ) t1
  group by item_id

The inner select get the max created by user_id, the other get all the rows that macht and the outer buil the avg on this group by item_id

and with the your new condition on item_id you could use

   select item_id, avg( rating) from (
      select * from items_ratings  a 
      inner join (
      SELECT MAX(created) t_created, user_id, item_id
      FROM items_ratings
      GROUP BY user_id, item_id
        ) t on  t.user_id = a.user_id and  t.t_created = a.created  and  t.item_id = a.item_id
      ) t1
  group by item_id