Respuesta a la Trivia de SQL Básico

En octubre lancé una trivia para cerrar la serie de SQL básico. En este post vamos a revisar el proceso que usé para solucionar la trivia. Algunos de los pasos y consultas los realicé para poder, de cierta forma, corroborar si los resultados tenían sentido. La pregunta es la siguiente:

"Mostrar la consulta y el resultado que obtenga el par de actores que han trabajado en más films juntos. Por ejemplo, quizá Cristopher y Bela Walken han trabajado en 50 films. La consulta debe regresar a los dos actores y el número de films. NOTA: Cristopher no es necesariamente parte de la respuesta, puede ser cualquier par de actores."

Primero, obtengamos el número de registros en la tabla film_actor y film.

select count(*) from film_actor;

select count(*) from film;

Hay 1000 films y 5462 registros en film_actor. Es decir, hay en promedio 6 (5462/1000) actores por film. Si formamos parejas con los 6 actores de un film, tenemos un total de 30 parejas por film - cada actor hace pareja con los otros 5 actores. Noten, que para un mismo film una pareja aparece 2 veces: la pareja Actor A con Actor B y la pareja Actor B con Actor A. Esto último no afecta el resultado final, pero es importante tenerlo en cuenta.

Dado que hay 1000 films y aproximadamente 30 parejas por film, la primer consulta que vamos a construir debe regresar alrededor de 30,000 registros correspondientes a todas las parejas de todos los films. La siguiente consulta cuenta todas las parejas:

select count(*)
from actor
    inner join film_actor on (actor.actor_id = film_actor.actor_id)
    inner join film_actor colega on 
               (film_actor.film_id=colega.film_id and 
                actor.actor_id <> colega.actor_id)
    inner join actor actor_colega on 
               (colega.actor_id=actor_colega.actor_id)

Esta consulta regresa 29,830; lo cual es muy cercano al estimado que calculamos arriba. Ahora, analicemos los joins de la consulta. El primer join relaciona un actor con los films en los que ha participado. El segundo y tercer join relacionan a un actor con todos sus colegas a lo largo de todas los films en los que ha participado. Veamos un ejemplo, la siguiente consulta regresa los actores que participaron en el film 500

select actor.first_name, actor.last_name
from actor
    inner join film_actor on (actor.actor_id = film_actor.actor_id)
where film_actor.film_id=500

Ahora agreguemos los dos joins para calcular las parejas de ese film. Noten que este film tiene 7 actores, por lo que habrá 42 parejas.

select actor.first_name, actor.last_name, 
       actor_colega.first_name, actor_colega.last_name
from actor
    inner join film_actor on (actor.actor_id = film_actor.actor_id)
    inner join film_actor colega on 
               (film_actor.film_id=colega.film_id and 
                actor.actor_id <> colega.actor_id)
    inner join actor actor_colega on 
               (colega.actor_id=actor_colega.actor_id)
where film_actor.film_id=500

La siguiente imagen muestra todas las parejas para el film 500. Observen que todas las parejas aparece dos veces, por ejemplo: la pareja CUBA OLIVIER y HELEN VOIGHT y la pareja HELEN VOIGHT y CUBA OLIVIER. Sin embargo, como mencioné arriba, esto no afectará el resultado final.

Muy bien, ya tenemos las parejas, sólo tenemos que agrupar y contar. La siguiente consulta despliega la pareja que más films ha hecho.

select actor.first_name, actor.last_name, 
       actor_colega.first_name, actor_colega.last_name, 
       count(*) as n
from actor
    inner join film_actor on (actor.actor_id = film_actor.actor_id)
    inner join film_actor colega on 
               (film_actor.film_id=colega.film_id and 
                actor.actor_id <> colega.actor_id)
    inner join actor actor_colega on 
               (colega.actor_id=actor_colega.actor_id)
group by actor.first_name, actor.last_name, 
         actor_colega.first_name, actor_colega.last_name
order by n DESC
LIMIT 1

Y los ganadores son HENRY BERRY y JULIA MCQUEEN con 7 films juntos. Se puede dar el caso de que haya más de una pareja con 7 films. Si en la consulta anterior, cambias a LIMIT 10, podrás ver las primeras 10 filas y notarás que después de la pareja ganadora, siguen otras parejas con sólo 6 films.