https://www.edgedb.com/blog/edgedb-1-0

edgedb.sql

/*
Task: get a set of movies where Zendaya played a role, 
and for every such movie calculate the average review score, 
and also retrieve the list of top 5 actors in the order of credits. 

Here’s how such query can be written in EdgeQL: 

select
  Movie {
    title,
    rating := math::mean(.ratings.score)
    actors: {
      name
    } order by @credits_order
      limit 5,
  }
filter
  "Zendaya" in .actors.name
*/

--Database
create table movies (
  movie_id integer primary key,
  title text
);

create table actors (
  actor_id integer primary key,
  name text
);

create table movies_actors (
  movie_id integer,
  actor_id integer,
  credits_order integer
);

create table reviews (
  review_id integer primary key,
  movie_id integer,
  score integer
);

insert into movies (movie_id, title)
values
(1, 'Spider-Man: No Way Home'),
(2, 'Dune'),
(3, 'Avengers: Endgame');

insert into actors (actor_id, name)
values
(11, 'Zendaya'),
(12, 'Tom Holland');

insert into movies_actors (movie_id, actor_id, credits_order)
values (1, 11, 1), (1, 12, 2), (2, 11, 1), (3, 12, 2);

insert into reviews (review_id, movie_id, score)
values
(101, 1, 10),
(102, 1, 10),
(103, 1, 10),
(104, 2, 10),
(105, 2, 8),
(106, 2, 8),
(107, 3, 10),
(108, 3, 9),
(109, 3, 9);

-- Edge DB example from article:
SELECT
  title,
  Actors.name AS actor_name,
  (SELECT avg(score)
   FROM Movie_Reviews
   WHERE movie_id = Movie.id) AS rating
FROM
  Movie
  CROSS JOIN LATERAL (
    SELECT name
    FROM
      Movie_Actors
      INNER JOIN Person
        ON Movie_Actors.person_id = Person.id
      WHERE Movie_Actors.movie_id = Movie.id
      ORDER BY Movie_Actors.credits_order
      FETCH FIRST 5 ROWS ONLY
   ) AS Actors
WHERE
  'Zendaya' IN (
    SELECT Person.name
    FROM
      Movie_Actors
      INNER JOIN Person
        ON Movie_Actors.person_id = Person.id
    WHERE
      Movie_Actors.movie_id = Movie.id
  )
  
-- My example:
select
    title,
    name,
    avg(score),
    top_5_actors
from movies m
    join movies_actors using(movie_id)
    join actors a using(actor_id)
    join (
        select movie_id
        from actors join movies_actors using(actor_id)
        where actors.name = 'Zendaya'
    ) sub1 using(movie_id)
    left outer join reviews using(movie_id)
    join lateral (
        select array_agg(name order by credits_order) as top_5_actors
        from actors join movies_actors using(actor_id)
        where movies_actors.movie_id = m.movie_id
        limit 5
    ) sub2 on true
group by 
    title,
    name,
    top_5_actors