1050. Actors and Directors Who Cooperated At Least Three Times
Description
Table: ActorDirector
+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp is the primary key (column with unique values) for this table.
Write a solution to find all the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: ActorDirector table: +-------------+-------------+-------------+ | actor_id | director_id | timestamp | +-------------+-------------+-------------+ | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 1 | 6 | +-------------+-------------+-------------+ Output: +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ Explanation: The only pair is (1, 1) where they cooperated exactly 3 times.
Solutions
Solution 1: Group By + Having
Thinking
We need how often each actor–director pair collaborated. Grouping by both ids makes the group size the collaboration count.
GROUP BY actor_id, director_id and HAVING COUNT(1) >= 3 keep pairs with at least three rows.
One aggregation is enough; no self-join is required.
We can use the GROUP BY statement to group the data by the actor_id and director_id fields, and then use the HAVING statement to filter out the actor_id and director_id that appear at least three times.
1 2 3 4 5 | |