Retrieve average values for the earliest date

This

SELECT ...
FROM reviews
HAVING MIN(review_date)

cannot work. Let’s say the minimum date in the table is DATE '2020-01-01', then what is HAVING DATE '2020-01-01' supposed to mean?

This

SELECT ...
FROM reviews
WHERE review_date IN (SELECT MIN(review_date) FROM reviews GROUP BY id);

is close, but it’s not the minimum date per ID, but the minimum date per user ID you want. And if you replace id by user_id, then there is still a problem, because what is the first date for one user can be the third date for another.

Here is this query corrected:

SELECT
  AVG(overall_rating), AVG(rooms_rating),
  AVG(service_rating), AVG(location_rating), AVG(value_rating)
FROM reviews
WHERE (user_id, review_date) IN 
         (SELECT user_id, MIN(review_date) FROM reviews GROUP BY user_id);