Identify Frequent Actor-Director Collaborations in Film Database
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates competency in relational data manipulation and aggregation, specifically the ability to identify repeated actor–director relationships using SQL or Python data tools.
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).
Tables
FILMOGRAPHY(actor VARCHAR(50), dir VARCHAR(50), date DATE)
Hints
- GROUP BY actor, dir
- Use HAVING COUNT(*) >= 3