Actors And Directors Who Cooperated At Least Three Times - Leetcode Solution

Problem Statement:

Given two tables: actor and director, with columns id and name. Table actor:

+------+-------+
| id   | name  |
+------+-------+
| 1    | A     |
| 2    | B     |
| 3    | C     |
| 4    | D     |
| 5    | E     |
+------+-------+

Table director:

+------+-------+
| id   | name  |
+------+-------+
| 1    | X     |
| 2    | Y     |
| 3    | Z     |
| 4    | W     |
+------+-------+

Write a SQL query to find the pairs (actor, director) who have cooperated at least three times.

Solution:

To find the pairs (actor, director) who have cooperated at least three times, we need to join the actor and director tables with another table, say cooperation, that contains information about their collaborations. The cooperation table should have at least two columns, actor_id and director_id, to represent the actors and directors involved in the collaborations. Each row in the cooperation table should represent a single collaboration between an actor and a director. To find the pairs who have cooperated at least three times, we need to count the number of collaborations for each pair and filter out the pairs who have cooperated less than three times. We can do this by using the GROUP BY clause and the HAVING clause.

Here's the SQL query to solve this problem:

SELECT actor.name AS actor_name, director.name AS director_name, COUNT(*) AS collaborations
FROM actor
JOIN cooperation ON actor.id = cooperation.actor_id
JOIN director ON cooperation.director_id = director.id
GROUP BY actor_name, director_name
HAVING collaborations >= 3;

Explanation:

The SQL query first joins the actor table with the cooperation table on the actor_id column, and then joins the result with the director table on the director_id column. The resulting table contains the names of the actors and directors who have cooperated, as well as the number of times they have cooperated. The GROUP BY clause groups the rows by actor and director name, and the COUNT(*) function counts the number of rows in each group, which represents the number of collaborations for each actor-director pair. The HAVING clause filters out the pairs who have cooperated less than three times, leaving only the pairs who have cooperated at least three times. Finally, the SELECT clause selects the actor and director names and the number of collaborations.

1