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

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` | Column | Type | Meaning | | --- | --- | --- | | `object_id` | BIGINT | Primary key; identifies either a movie or a person | | `object_type` | TEXT | `'movie'` or `'person'` | | `name` | TEXT | Movie title or person name | `credits` | Column | Type | Meaning | | --- | --- | --- | | `movie_object_id` | BIGINT | References `objects.object_id`; expected to be a movie | | `person_object_id` | BIGINT | References `objects.object_id`; expected to be a person | | `role` | TEXT | For 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. ```hint Count the right thing Consider what a single `credits` row represents, and how many rows one person can produce for one movie. ``` #### 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. ```hint Filter before you aggregate Decide which rows should survive before grouping, and what the aggregate should be distinct over. ``` #### 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. ### What a Strong Answer Covers - Clarifying the popularity definition and the tie policy before coding. - Filtering on role and deduplicating per person and movie, so multi-role people are not overcounted. - Using object types to keep movies and people from being confused in a shared ID space. - Handling dirty data such as unnormalized role values and credits that point to objects of the wrong type. ### Follow-up Questions 1. 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? 2. The credits table holds billions of rows and the ranking is requested often. How would you precompute or maintain it? 3. How would you report the most popular directors and actors in one query, with a separate ranking for each role?