coding_challenge_2g.sql

WITH subq (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 actor_1_id, actor_2_id, COUNT(film_id) AS count
FROM subq
GROUP BY actor_1_id, actor_2_id
HAVING COUNT(film_id) = (
  SELECT MAX(xxx.mycount)
  FROM (
    SELECT actor_1_id, actor_2_id, COUNT(film_id) AS mycount
    FROM subq
    GROUP BY actor_1_id, actor_2_id
  ) AS xxx
)