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
Actor-Director Collaboration AnalysisStep 1: Input Dataactor_iddirector_idtimestamp110111112123GROUP BYCOUNT(*)Step 2: Group & Countactor_iddirector_idcount113121HAVINGcount >= 3Step 3: Final Resultactor_iddirector_id11SQL Query ExplanationSELECT actor_id, director_idFROM ActorDirectorGROUP BY actor_id, director_idHAVING COUNT(*) >= 3;← Select the actor-director pairs← From the collaboration table← Group by each unique pair← Keep only pairs with 3+ collaborations
Understanding the Visualization
1
Input Data
ActorDirector table with collaboration records
2
Group & Count
GROUP BY actor-director pairs and count collaborations
3
Filter Results
HAVING clause keeps pairs with 3+ collaborations
Key Takeaway
🎯 Key Insight: Use GROUP BY to aggregate data and HAVING to filter groups based on aggregate conditions

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