Create a view (virtual table) called good_collaboration that lists pairs of actors who have had a good collaboration as defined here. Each row in the view describes one pair of actors who appeared in at least 3 movies together AND the average score of these movies is >= 40.

 

The view should have the format:

good_collaboration

( cast_member_id1,

cast_member_id2,

movie_count,

average_movie_score)

 

For symmetrical or mirror pairs, only keep the row in which cast_member_id1 has a lower numeric value. For example, for ID pairs (1, 2) and (2, 1), keep the row with IDs (1, 2). There should not be any "self-pair" where the value of cast_member_id1 is the same as that of cast_member_id2.

 

i. [4 points] Find the best collaborators. Get the 5 cast members with the highest average scores from the good_collaboration view, and call this score the collaboration_score. This score is the average of the average_movie_score corresponding to each cast member, including actors in cast_member_id1 as well as cast_member_id2. Format all decimals to two places using printf().

 

• Order your output by collaboration_score (before formatting) in descending order, then by cast_name alphabetically.

 

Output format and example values(cast_id,cast_name,collaboration_score):

2,Mark Hamil,99.32

1920,Winoa Ryder,88.32

 

 

Answers:

def part_g(self,connection):
    ############### EDIT SQL STATEMENT ###################################
    part_g_sql = """
    """
    ######################################################################
    return self.execute_query(connection, part_g_sql)

def part_gi(self,connection):
    ############### EDIT SQL STATEMENT ###################################
    part_g_i_sql = """
   
    """
    ######################################################################
    cursor = connection.execute(part_g_i_sql)
    return cursor.fetchall()