Identify Frequent Actor-Director Collaborations in Film Database

FILMOGRAPHY

+-------+---------+------------+
| actor | dir     | date       |
+-------+---------+------------+
| Tom   | Nolan   | 2020-01-01 |
| Tom   | Nolan   | 2021-01-01 |
| Tom   | Nolan   | 2022-01-01 |
| Amy   | Nolan   | 2021-07-01 |
+-------+---------+------------+

Scenario

Film industry database: find frequent actor–director collaborations

Question

Write an SQL query to list each actor–director pair that has worked together at least three times, returning actor, dir, and collaboration_count.

Hints

GROUP BY actor, dir; use HAVING COUNT(*) >= 3.

Frequent actor–director collaborations

Given a FILMOGRAPHY table of films with their actor, director, and release date, write an SQL query to list each actor–director pair that has worked together at least three times. Return the columns actor, dir, and collaboration_count (the number of films they have worked on together).

Table FILMOGRAPHY

column	type	constraints
actor	VARCHAR(50)	NOT NULL
dir	VARCHAR(50)	NOT NULL
date	DATE	NOT NULL

Sample data:

actor	date	dir
Tom	2020-01-01	Nolan
Tom	2021-01-01	Nolan
Tom	2022-01-01	Nolan
Amy	2021-07-01	Nolan

Expected output

One row per actor–director pair that has collaborated on three or more films, including the count of collaborations.

actor	collaboration_count	dir
Tom	3	Nolan

Hints

1. GROUP BY actor, dir
2. Use HAVING COUNT(*) >= 3