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

Next Greater Element

A Gentle Introduction to Elixir

Managing Azure Roles & Permissions

POST-MORTEM

Apache Spark, What and Why

FORMULA 1 STORIES : SAUDI ARABIA GP #1

An introduction to Redis’ PubSub system

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
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Embedded SQL and Dynamic SQL a

T-SQL :: DELETE All Data in Schema

Your SQL Server Colleagues

SQL Queries

5 Advance SQL functions you must know