Actors and Directors Who Cooperated At Least Three Times

Difficulty: Easy | Acceptance: 71.10% | Paid: No Topics: Database

Table: ActorDirector +-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp is the primary key column for this table.

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.

• Examples
• Constraints
• Approach 1: Group By and Having
• Approach 2: Subquery with Count

Examples

Example 1

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 | +-------------+-------------+

Explanation: The only pair (1, 1) appears at least 3 times.

Constraints

- 1 <= actor_id, director_id <= 10⁴
- 0 <= timestamp <= 10⁵
- Each row in the table represents a unique collaboration

Group By and Having

Intuition Group the data by actor and director pairs, then filter for groups with at least 3 records using the HAVING clause.

Steps

• Select actor_id and director_id from the table
• Group by both columns to create unique pairs
• Use HAVING COUNT(*) >= 3 to filter pairs with 3 or more collaborations

import sqlite3

def find_cooperating_pairs():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    
    cursor.execute("""
        CREATE TABLE ActorDirector (
            actor_id INT,
            director_id INT,
            timestamp INT PRIMARY KEY
        )
    """)
    
    query = """
        SELECT actor_id, director_id
        FROM ActorDirector
        GROUP BY actor_id, director_id
        HAVING COUNT(*) >= 3
    """
    
    cursor.execute(query)
    results = cursor.fetchall()
    return results

print(find_cooperating_pairs())

Complexity

• Time: O(n) where n is the number of rows in ActorDirector table
• Space: O(k) where k is the number of unique actor-director pairs
• Notes: This is the most efficient and idiomatic SQL approach for this problem

Subquery with Count

Intuition First count collaborations for each pair in a subquery, then filter pairs with count >= 3 in the outer query.

Steps

• Create a subquery that counts collaborations for each actor-director pair
• Filter the results where count is at least 3
• Return the actor_id and director_id

import sqlite3

def find_cooperating_pairs():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    
    cursor.execute("""
        CREATE TABLE ActorDirector (
            actor_id INT,
            director_id INT,
            timestamp INT PRIMARY KEY
        )
    """)
    
    query = """
        SELECT actor_id, director_id
        FROM (
            SELECT actor_id, director_id, COUNT(*) as cnt
            FROM ActorDirector
            GROUP BY actor_id, director_id
        ) AS pairs
        WHERE cnt >= 3
    """
    
    cursor.execute(query)
    results = cursor.fetchall()
    return results

print(find_cooperating_pairs())

Complexity

• Time: O(n) where n is the number of rows in ActorDirector table
• Space: O(k) where k is the number of unique actor-director pairs
• Notes: Slightly more verbose than GROUP BY with HAVING but achieves the same result