Actors and Directors Who Cooperated At Least Three Times

Actors and Directors Who Cooperated At Least Three Times

What is this problem about?

The Actors and Directors Who Cooperated At Least Three Times coding problem is a straightforward relational data task. You have a table recording every time an actor and a director worked together on a project. You need to identify pairs of (Actor, Director) who have collaborated at least three times.

Why is this asked in interviews?

Companies like Microsoft and Amazon use this Actors and Directors interview question to verify basic SQL fluency. It assesses your ability to use multi-column grouping and filtering on aggregated data, which are fundamental skills for any data-focused role.

Algorithmic pattern used

The pattern here is the Group By and Having interview pattern. Unlike a simple group by on one column, you group by a pair of columns to find the intersection of two entities and then use the HAVING clause to filter the result based on the count.

Example explanation

Consider the following ActorDirector table:

• Actor 1, Director 1, Timestamp 1
• Actor 1, Director 1, Timestamp 2
• Actor 1, Director 2, Timestamp 3
• Actor 1, Director 1, Timestamp 4

When we group by (actor_id, director_id):

• Pair (1, 1) has a count of 3.
• Pair (1, 2) has a count of 1. Since 3 $\geq$ 3, the result is the pair (1, 1).

Common mistakes candidates make

• Grouping by one ID: Grouping only by actor_id or only by director_id. This would count total movies for an individual, not collaborations between the two.
• WHERE vs HAVING: Attempting to use a WHERE clause for the count. Remember: WHERE filters rows before grouping, while HAVING filters after grouping.
• Selecting extra columns: Including columns in the SELECT list that are not in the GROUP BY clause, which causes errors in most SQL environments.

Interview preparation tip

Always remember the SQL execution order: FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT. Knowing this prevents you from trying to filter aggregates in the wrong place.