4 kyu

Given the the schema presented below find two actors who cast together the most and list titles of only those movies they were casting together. Order the result set alphabetically by the movie title.

Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id | smallint | not null
film_id | smallint | not null
...
Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id | integer | not null
first_name | character varying(45) | not null
last_name | character varying(45) | not null
...
Column     | Type                        | Modifiers
------------+-----------------------------+----------
film_id | integer | not null
title | character varying(255) | not null
...
first_actor | second_actor | title
------------+--------------+--------------------
John Doe | Jane Doe | The Best Movie Ever
...
  • first_actor - Full name (First name + Last name separated by a space)
  • second_actor - Full name (First name + Last name separated by a space)
  • title - Movie title

Note: actor_id of the first_actor should be lower then actor_id of the second_actor

Solution:

select concat(a1.first_name,' ', a1.last_name) as second_actor,
concat(a2.first_name,' ', a2.last_name) as first_actor,
f.title
from
(
select r1.actor_id as actor_a_id,
r2.actor_id as actor_b_id,
count(r1.film_id) as casted_together,
array_agg(r1.film_id) as film_ids
from film_actor r1
join film_actor r2
on r1.film_id = r2.film_id -- they cast together
and r1.actor_id > r2.actor_id
group by r1.actor_id, r2.actor_id
order by casted_together desc
limit 1
) s
join actor a1 on a1.actor_id = s.actor_a_id
join actor a2 on a2.actor_id = s.actor_b_id
join film f on f.film_id = any(s.film_ids)

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store