Description
Write a SQL query to find actor-director pairs who collaborated at least 3 times. Return actor_id, director_id.
actor_director| Column | Type |
|---|---|
| actor_id | INT |
| director_id | INT |
| timestamp | INT |
Examples
CREATE TABLE actor_director (actor_id INT, director_id INT, timestamp INT); INSERT INTO actor_director VALUES (1, 1, 0), (1, 1, 1), (1, 1, 2), (1, 2, 3), (2, 1, 4);1|1Actor 1 collaborated with Director 1 three times (timestamps 0, 1, 2). Actor 1 with Director 2 has only 1 collaboration. Actor 2 with Director 1 has only 1 collaboration.
CREATE TABLE actor_director (actor_id INT, director_id INT, timestamp INT); INSERT INTO actor_director VALUES (2, 3, 100), (2, 3, 200), (2, 3, 300), (2, 3, 400), (3, 4, 500), (3, 4, 600), (4, 5, 700), (4, 5, 800);2|3Actor 2 and Director 3 collaborated 4 times, which meets the requirement of at least 3 collaborations. Actor 3 with Director 4 only collaborated twice, and Actor 4 with Director 5 only collaborated twice, so they don't qualify.
CREATE TABLE actor_director (actor_id INT, director_id INT, timestamp INT); INSERT INTO actor_director VALUES (5, 6, 1000), (5, 6, 2000), (5, 6, 3000), (5, 7, 4000), (5, 7, 5000), (5, 7, 6000), (6, 8, 7000), (6, 8, 8000);5|6
5|7Actor 5 collaborated with Director 6 exactly 3 times and with Director 7 exactly 3 times, both meeting the minimum threshold. Actor 6 with Director 8 only collaborated twice, so they don't qualify. This shows that one actor can have multiple qualifying partnerships.
Constraints
- •
Use GROUP BY and HAVING