How do i get instance of a model with highest value from an operation?

https://stackoverflow.com/questions/70868159/how-do-i-get-instance-of-a-model-with-highest-value-from-an-operation/70869517?noredirect=1#comment125324396_70869517

Director.create(first_name: "Martin Scorsese", date_of_birth: "17/11/1942", id: 2)
Director.create(first_name: "Michael Bay",     date_of_birth: "17/02/1965", id: 3)
Director.create(first_name: "George Lucas",    date_of_birth: "14/05/1944", id: 1)

Movie.create(name: "Star Wars: Epizoda IV – Nová naděje",   release_date: "25/05/1977", description: "", director_id: 1)
Movie.create(name: "Star Wars: Epizoda III – Pomsta Sithů", release_date: "15/05/2005", description: "", director_id: 1)
Movie.create(name: "Skrytá identita",                       release_date: "06/10/2006", description: "", director_id: 2)
Movie.create(name: "Mizerové",                              release_date: "07/04/1995", description: "", director_id: 3)

Rating.create(value: 4, movie_id:1) # 3
Rating.create(value: 3, movie_id:1) # 3
Rating.create(value: 3, movie_id:2) # 4
Rating.create(value: 5, movie_id:2) # 4
Rating.create(value: 4, movie_id:3) # 3
Rating.create(value: 4, movie_id:3) # 3
Rating.create(value: 3, movie_id:3) # 3
Rating.create(value: 5, movie_id:4) # 4
Rating.create(value: 4, movie_id:4) # 4
Rating.create(value: 4, movie_id:4) # 4

ActiveRecord::Base.connection.execute(<<~SQL).entries
SELECT
  d.*,
  (
    SELECT t.name
    FROM (
      SELECT name, (SELECT AVG(value) FROM ratings WHERE movie_id = movies.id) avgval
      FROM movies
      WHERE director_id = d.id
      ORDER BY avgval DESC
      LIMIT 1
    ) t
  ) movie_name
FROM directors d;
SQL

# [{"id"=>2, "first_name"=>"Martin Scorsese", "date_of_birth"=>"1942-11-17", "created_at"=>2022-01-28 17:48:33.086789 UTC, "updated_at"=>2022-01-28 17:48:33.086789 UTC, "movie_name"=>"Skrytá identita"},
#  {"id"=>3, "first_name"=>"Michael Bay",     "date_of_birth"=>"1965-02-17", "created_at"=>2022-01-28 17:48:33.104866 UTC, "updated_at"=>2022-01-28 17:48:33.104866 UTC, "movie_name"=>"Mizerové"},
#  {"id"=>1, "first_name"=>"George Lucas",    "date_of_birth"=>"1944-05-14", "created_at"=>2022-01-28 17:48:33.111349 UTC, "updated_at"=>2022-01-28 17:48:33.111349 UTC, "movie_name"=>"Star Wars: Epizoda III – Pomsta Sithů"}]

"Star Wars: Epizoda III – Pomsta Sithů" averages 4 while "Star Wars: Epizoda IV – Nová naděje" averages 3, so the result is as expected.

Rails::VERSION::STRING
# => "7.0.1"

RUBY_VERSION
=> "3.0.2"

$ psql
select version();
                                                               version
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)