Welcome to Subscribe On Youtube
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
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.
-
# Write your MySQL query statement below SELECT actor_id, director_id FROM ActorDirector GROUP BY 1, 2 HAVING COUNT(1) >= 3;