Description

Table: ActorDirector

Column NameType
actor_idint
director_idint
timestampint
  • timestamp is the primary key column for this table.

Problem Statement

Write a SQL query for a report that provides 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 query result format is in the following example.

Example 1:

Input:

  • ActorDirector table:
actor_iddirector_idtimestamp
110
111
112
123
124
215
216

Output:

actor_iddirector_id
11

Explanation:

  • The only pair is (1, 1) where they cooperated exactly 3 times.

Solution

You can find if the actor and director has cooperated by looking at the ActorDirector table. In this case, the timestamp field is primary key, so you simply need to aggregate the number of times the actor and director have cooperated. You can do this by simply counting the number of times the actor_id and director_id appear in the table.

1SELECT actor_id, director_id
2    FROM ActorDirector
3    GROUP BY actor_id, director_id
4    HAVING COUNT(1) >= 3;