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?
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
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);
CLICK HERE to find out more related problems solutions.