Codewars SQL

Challenge: Two actors who cast together the most

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.

Table film_actor

Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | smallint                    | not null
film_id     | smallint                    | not null
...

Table actor

Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | integer                     | not null 
first_name  | character varying(45)       | not null
last_name   | character varying(45)       | not null
...

Table film

Column     | Type                        | Modifiers
------------+-----------------------------+----------
film_id     | integer                     | not null
title       | character varying(255)      | not null
...

The desired output:

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