Actors and Directors Who Cooperated At Least Three Times is the question asked in many interviews and listed on leetcode at 596. 


Let's start with SQL Schema for the table named cinema:


Table ActorDirector:


CREATE TABLE ActorDirector (actor_id int, director_id int, timestamp int)


INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '0')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '1')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '2')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '3')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '4')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '5')

INSERT INTO ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '6')



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


Question: 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 will be:



actor_id director_id
1 1


You have the table and question to find all the classes that have at least five students.




Now, let's start with the Algorithm:


Let's first get the actor_id, and distributor_id and count with the group by on actor_id, and distributor_id to get how many times they appear in distinct combinations.

SELECT actor_id, director_id, COUNT(*) 
  FROM actordirector
    GROUP BY actor_id, director_id;


The output of the above query will look like this:



actor_id director_id COUNT(*)
1 1 3
1 2 2
2 1 2


Now, according to the question who cooperated with the director at least three times.

So, the query will be:

SELECT actor_id, director_id, COUNT(*) 
  FROM actordirector
    GROUP BY actor_id, director_id;
      HAVING COUNT(*)>=3


The output of the above query will look like this:




actor_id director_id COUNT(*)
1 1 3



But output should have actor_id and director_id only, so the query will be:

SELECT actor_id, director_id
  FROM actordirector
    GROUP BY actor_id, director_id;
      HAVING COUNT(*)>=3


This is the final query that will produce the desired output.


I hope you understood the algorithm if you have any queries or doubts please let us know in the comment section.