Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE movies (
  id    INT,
  title VARCHAR(16),
  genre VARCHAR(16)
);

INSERT INTO
  movies
VALUES
  (1, 'Return', 'Comedy'),
  (2, 'Scooby', 'Documentary'),
  (3, 'Halo',   'Documentary')
;

CREATE TABLE



INSERT 0 3


CREATE TABLE reviews (
  movie_id   INT,
  rating     INT
);

INSERT INTO
  reviews
VALUES
  (1, 1),
  (1, 3),
  (2, 5),
  (2, 5),
  (3, 3);

CREATE TABLE



INSERT 0 5


SELECT DISTINCT ON (m.genre)
  m.genre,
  m.title,
  ROUND(AVG(r.rating), 2)  AS stars
FROM
  movies    AS m
INNER JOIN
  reviews   AS r
    ON r.movie_id = m.id
GROUP BY
  m.genre,
  m.title
ORDER BY
  m.genre,
  AVG(r.rating) DESC


genre
title
stars


Comedy
Return
2.00

Documentary
Scooby
5.00



SELECT 2