Trying to create query with Sakila that finds for each customer X and another customer Y both of who have rented at least 5 movies from the same actor, such that the actor has acted in more than 5 movies. Find all such pairs of Customers (X, Y) and against each pair, the actor name and the number of shared movies of those actors by those customers. With four columns. I cannot figure how to make the run test work and what I am doing wrong. Right now, I have:
CREATE VIEW CustomerFilmActor AS
(SELECT customer_id, film_id, actor_id
FROM rental
JOIN inventory ON rental.inventory_id = inventory.inventory_id
GROUP BY customer_id, film_id, actor_id
ORDER BY customer_id);
SELECT c1.customer_id AS cid1, c2.customer_id AS cid2,
COUNT(c1.film_id) AS ActorMovies
FROM CustomerActorDetails c1
JOIN ActorMovies a ON c1.actor_id = c2.actor_id
GROUP BY cid1, cid2
HAVING COUNT(c1.film_id) >= 5
AND cid1 > cid2
ORDER BY COUNT(c1.film_id) DESC;
