Actors and Directors Who Cooperated At Least Three Times

Actors and Directors Who Cooperated At Least Three Times - Problem

Database Easy

🎬 Finding Frequent Actor-Director Collaborations

In the entertainment industry, successful partnerships between actors and directors often lead to multiple collaborations. Your task is to analyze a database of movie collaborations and identify the most frequent partnerships.

You are given a table ActorDirector that records every collaboration between actors and directors:

Column Name	Type	Description
`actor_id`	int	Unique identifier for the actor
`director_id`	int	Unique identifier for the director
`timestamp`	int	When the movie was made (primary key)

Goal: Find all pairs (actor_id, director_id) where the actor has worked with the director on at least 3 movies.

Example: If actor 1 worked with director 1 on movies recorded at timestamps 0, 1, and 2, then this pair should be included in the result because they collaborated 3 times.

Return the result in any order. The focus is on identifying these power partnerships in the film industry!

Input & Output

example_1.sql — Basic Collaboration

$ Input: ActorDirector table: | 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 |

› Output: | actor_id | director_id | |----------|-------------| | 1 | 1 |

💡 Note: Actor 1 worked with Director 1 three times (timestamps 0, 1, 2), which meets our threshold of at least 3 collaborations. All other pairs have fewer than 3 collaborations.

example_2.sql — Multiple Qualifying Pairs

$ Input: ActorDirector table: | actor_id | director_id | timestamp | |----------|-------------|----------| | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 2 | 2 | 3 | | 2 | 2 | 4 | | 2 | 2 | 5 | | 2 | 2 | 6 |

› Output: | actor_id | director_id | |----------|-------------| | 1 | 1 | | 2 | 2 |

💡 Note: Both pairs qualify: Actor 1 with Director 1 (3 collaborations) and Actor 2 with Director 2 (4 collaborations). Both meet the minimum requirement of 3 collaborations.

example_3.sql — Edge Case: No Qualifying Pairs

$ Input: ActorDirector table: | actor_id | director_id | timestamp | |----------|-------------|----------| | 1 | 1 | 0 | | 1 | 2 | 1 | | 2 | 1 | 2 | | 2 | 3 | 3 | | 3 | 1 | 4 |

› Output: Empty result set | actor_id | director_id | |----------|-------------|| | | |

💡 Note: No actor-director pair appears 3 or more times. Each combination appears only once or twice, so no pairs qualify for the result.

Visualization

Tap to expand

Understanding the Visualization

Raw Data Collection

Each movie collaboration is recorded with actor, director, and timestamp

Group by Partnerships

SQL groups identical actor-director pairs together

Count Collaborations

For each group, count total number of movies made together

Filter Frequent Pairs

Keep only pairs with 3+ collaborations using HAVING clause

Key Takeaway

🎯 Key Insight: GROUP BY with HAVING is the perfect tool for finding patterns in relational data - it groups, counts, and filters all in one efficient operation!

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data to group and count

✓ Linear Growth

Space Complexity

O(k)

Space for k distinct actor-director pairs in the hash table

✓ Linear Space

Constraints

1 ≤ Number of rows ≤ 1000
1 ≤ actor_id, director_id ≤ 500
0 ≤ timestamp ≤ 10⁸
timestamp is the primary key (each movie is unique)

Asked in

G Google 35 a Amazon 28 f Meta 22 ⊞ Microsoft 18

The optimal solution uses GROUP BY to group records by actor-director pairs and HAVING COUNT(*) >= 3 to filter for pairs with at least 3 collaborations. This approach is efficient with O(n) time complexity and leverages SQL's built-in aggregation capabilities.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n²)	O(n)	Use nested queries to count collaborations for each possible pair
GROUP BY with HAVING (Optimal)	O(n)	O(k)	Group by actor-director pairs and count collaborations in single pass

Nested Subqueries Approach — Algorithm Steps

Find all distinct actor-director pairs
For each pair, count total collaborations using a subquery
Filter pairs with count >= 3
Return the qualifying pairs

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Distinct Pairs

Identify all unique actor-director combinations

Count for Each Pair

For each pair, scan entire table to count occurrences

Filter Results

Keep only pairs with count >= 3

Code -

solution.c — C

// SQL solution using nested subqueries
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sql[] = "\
        SELECT DISTINCT a1.actor_id, a1.director_id\
        FROM ActorDirector a1\
        WHERE (\
            SELECT COUNT(*)\
            FROM ActorDirector a2\
            WHERE a2.actor_id = a1.actor_id\
            AND a2.director_id = a1.director_id\
        ) >= 3";
    return sql;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each distinct pair, we scan the entire table again to count collaborations

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing distinct pairs and intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 1000
1 ≤ actor_id, director_id ≤ 500
0 ≤ timestamp ≤ 10⁸
timestamp is the primary key (each movie is unique)

67.5K Views

High Frequency

~8 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🎬 Finding Frequent Actor-Director Collaborations

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler