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


select concat(a1.first_name,' ', a1.last_name) as second_actor,
concat(a2.first_name,' ', a2.last_name) as first_actor,
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)




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