coding_challenge_2i.sql

WITH subq1 (actor_1_id, actor_2_id, film_id) AS 
(SELECT p1.actor_id, p2.actor_id, p2.film_id
  FROM film_actor p1
     JOIN film_actor p2
     ON p1.film_id = p2.film_id
     AND p1.actor_id < p2.actor_id
)
SELECT 
  CONCAT(a_1.first_name, ' ' , a_1.last_name) AS first_actor, 
  CONCAT(a_2.first_name, ' ' ,a_2.last_name) AS second_actor, 
  film.title
FROM subq1
INNER JOIN 
( 
  SELECT subq1.actor_1_id, subq1.actor_2_id
    FROM subq1
    GROUP BY subq1.actor_1_id, subq1.actor_2_id
    HAVING COUNT(film_id) = (
      SELECT MAX(subq2.mycount)
      FROM (
        SELECT actor_1_id, actor_2_id, COUNT(film_id) AS mycount
        FROM subq1
        GROUP BY actor_1_id, actor_2_id
      ) AS subq2
    )
  ) AS subq3 ON subq3.actor_1_id=subq1.actor_1_id AND subq3.actor_2_id=subq1.actor_2_id
LEFT JOIN actor AS a_1 ON a_1.actor_id = subq1.actor_1_id
LEFT JOIN actor AS a_2 ON a_2.actor_id = subq1.actor_2_id
INNER JOIN film ON film.film_id = subq1.film_id
ORDER BY film.title