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

Hint: Use double quotes in SQL for field names.

Solution:

with top_name (actor1, actor2, starts) as
(select tp.actor_id, fa.actor_id as costar, count(fa.actor_id) as starts
from film_actor fa, film_actor tp
where fa.film_id = tp.film_id
and fa.actor_id <> tp.actor_id
group by tp.actor_id, fa.actor_id
order by starts desc, tp.actor_id
limit 1) -- the most co-star
-- join with actor and film tables
select a1.first_name||' '||a1.last_name as "first_actor",
a2.first_name||' '||a2.last_name as "second_actor",
f.title
from
actor a1, actor a2, film f, top_name tt
where tt.actor1 = a1.actor_id
and tt.actor2 = a2.actor_id
and f.film_id in
(select fa1.film_id from film_actor fa1, film_actor fa2
where fa1.film_id = fa2.film_id
and fa1.actor_id = a1.actor_id
and fa2.actor_id = a2.actor_id);

Other Solution:

with top_pair as (
select a1.actor_id as id1, a2.actor_id as id2
from film_actor a1
inner join film_actor a2 on a1.film_id=a2.film_id
where a1.actor_id <> a2.actor_id
group by a1.actor_id, a2.actor_id
order by count(a1.film_id) desc
limit 1
)
select
(select first_name || ' ' || last_name from actor where actor_id = tp.id1) as first_actor,
(select first_name || ' ' || last_name from actor where actor_id = tp.id2) as second_actor,
f.title as title
from top_pair tp
inner join film_actor fa1 on tp.id1 = fa1.actor_id
inner join film_actor fa2 on tp.id2 = fa2.actor_id
inner join film f on fa1.film_id=f.film_id and fa2.film_id=f.film_id

Link

Reference

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Testing asynchronous pipelines with fs2 and weaver-test

My Opinion on LeetCode:

Guess What...🤷

Building a real-life Pokedex

Cloud Computing

Authorization with Firebase Authentication, NodeJS and React

Let there be (blinky) light!

ClickHouse and Python: Jupyter Notebooks

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
Jen-Li Chen

Jen-Li Chen

In love with telling stories with data

More from Medium

SQL Server Indexes: Interview Questions

SQL - JOINS

What is SQL?