Katkat
Back

Most Popular Actors Query When Object IDs Mix Movies and People With Multiple Roles

ScribdSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteData Manipulation (SQL/Python)

Last updated

A data query question about ranking the most popular actors when an object ID can refer to either a movie or a person, and one person can hold several roles in the same movie. It tests clarifying the popularity metric, filtering to actor credits, deduplicating person and movie pairs, checking object types, and defining a tie policy.

You are given movie and person data in which an object_id can refer either to a movie or to a person. A person can hold several roles in the same movie, for example both actor and director, so every credit records a role. Write a query, or equivalent data-processing code, that finds the most popular actors, counting only actor credits.

In the interview, no real database was available and the code did not need to run; the reasoning and the handling of the data's traps mattered most. The exact tables and the definition of "popular" were not specified, so settle them first.

Constraints and Clarifications

Use this working data model unless the interviewer gives a different one:

objects

ColumnTypeMeaning
object_idBIGINTPrimary key; identifies either a movie or a person
object_typeTEXT'movie' or 'person'
nameTEXTMovie title or person name

credits

ColumnTypeMeaning
movie_object_idBIGINTReferences objects.object_id; expected to be a movie
person_object_idBIGINTReferences objects.object_id; expected to be a person
roleTEXTFor example 'actor' or 'director'
  • One person can have several credits rows for the same movie, with different roles and possibly more than one row with the 'actor' role.
  • Only actor credits count toward an actor's popularity.

Clarifying Questions

  • How is "popular" defined: the number of distinct movies a person acted in, or a popularity or engagement signal attached to the movies or to the person?
  • How many actors should be returned, and should ties at the cutoff all be included?
  • Are role values normalized, or can 'Actor', 'actor ', and NULL appear?
  • Is referential integrity guaranteed, or can a credit point to an object of the wrong type or to a missing object?

Part 1 — Model the Traps

Before writing the query, explain what goes wrong if you count credits rows directly, and how the shared object_id space for movies and people affects the joins.

What This Part Should Cover

  • How multiple roles, and repeated actor credits, can inflate a naive count.
  • Why the join must confirm which side of a credit is a movie and which is a person.
  • Which clarifications change the query and which do not.

Part 2 — Write the Aggregation

Assume the interviewer lets you define popularity as the number of distinct movies in which a person has at least one actor credit. Return the top 10 actors, with person_object_id, name, and movie_count, sorted by movie_count descending and then person_object_id ascending. Then explain how you would return every actor tied at the tenth position instead.

What This Part Should Cover

  • A correct actor-only, distinct-movie aggregation.
  • A deterministic ordering and an explicit tie policy.
  • An equivalent formulation in application code if SQL is not available.

Hints

Hint 1

Count the right thing: Consider what a single `credits` row represents, and how many rows one person can produce for one movie.

Hint 2

Filter before you aggregate: Decide which rows should survive before grouping, and what the aggregate should be distinct over.

Follow-up questions

  • Popularity should instead be the total number of engagement events on the movies an actor appeared in. How does the query change, and how do you avoid counting a movie's events more than once for the same actor?
  • The credits table holds billions of rows and the ranking is requested often. How would you precompute or maintain it?
  • How would you report the most popular directors and actors in one query, with a separate ranking for each role?