Actors and Directors Who Cooperated At Least Three Times - Problem

Given a table ActorDirector that contains information about actors and directors who have worked together on movies.

Write a SQL solution to find all pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.

Return the result table in any order.

Table Schema

ActorDirector
Column Name Type Description
actor_id int ID of the actor
director_id int ID of the director
timestamp PK int Timestamp when they worked together
Primary Key: timestamp
Note: Each row represents one collaboration between an actor and director. timestamp is unique for each collaboration.

Input & Output

Example 1 — Multiple Collaborations
Input 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
💡 Note:

Actor 1 worked with Director 1 three times (timestamps 0, 1, 2), which meets the requirement of at least 3 collaborations. Actor 1 worked with Director 2 only twice (timestamps 3, 4), and Actor 2 worked with Director 1 only twice (timestamps 5, 6), so these pairs don't qualify.
Example 2 — No Qualifying Pairs
Input Table:
actor_id director_id timestamp
1 1 0
1 2 1
2 1 2
2 2 3
Output:
actor_id director_id
💡 Note:

No actor-director pair has worked together 3 or more times. Each pair has collaborated only once, so no pairs meet the minimum requirement of 3 collaborations.
Example 3 — Multiple Qualifying Pairs
Input Table:
actor_id director_id timestamp
1 1 0
1 1 1
1 1 2
2 1 3
2 1 4
2 1 5
Output:
actor_id director_id
1 1
2 1
💡 Note:

Both Actor 1 and Actor 2 have worked with Director 1 exactly 3 times each, so both pairs qualify for the result.

Constraints

  • 1 ≤ actor_id, director_id ≤ 1000
  • 0 ≤ timestamp ≤ 10^9
  • timestamp values are unique

Visualization

Tap to expand
Actors & Directors Cooperation Finder 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 Find pairs with count >= 3 Cooperation Visualization A1 D1 D2 x3 ALGORITHM STEPS 1 GROUP BY Clause Group by (actor_id, director_id) 2 COUNT Aggregation Count rows per group 3 HAVING Filter Keep groups with count >= 3 4 SELECT Result Return actor_id, director_id SELECT actor_id, director_id FROM ActorDirector GROUP BY actor_id, director_id HAVING COUNT (*) >= 3 Counting Results (1,1): 3 OK - Include (1,2): 2 Skip - < 3 FINAL RESULT Output Table actor_id director_id 1 1 OK Actor 1 & Director 1 cooperated 3 times Cooperation Confirmed Actor 1 Director 1 3 Movies Qualified Pair Found! Result: 1 row returned Key Insight: Use GROUP BY with HAVING clause to efficiently filter aggregated results. The HAVING clause filters groups after aggregation (COUNT), while WHERE filters rows before grouping. Time Complexity: O(n) where n = number of rows | Space Complexity: O(m) where m = unique pairs TutorialsPoint - Actors and Directors Who Cooperated At Least Three Times | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
25.6K Views
Medium Frequency
~8 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen