Identify Most Popular First-Watched Movie in Viewing History
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
MOVIE_VIEWS
+------------+--------------------+------------+
| customer_id| title | date |
+------------+--------------------+------------+
| 1 | Toy Story |2025-01-01 |
| 1 | Avengers |2025-01-10 |
| 2 | Avengers |2025-01-02 |
+------------+--------------------+------------+
##### Scenario
A streaming platform wants to find the movie that most customers watched first in their viewing history.
##### Question
Using SQL or Python, determine which movie title appears most frequently as users' earliest-dated movie view (i.e., the most popular first-watched film).
##### Hints
Rank or aggregate by the minimum date per customer, then count titles and order by frequency.
Overview: This question evaluates proficiency in data manipulation and temporal event analysis, assessing competency in identifying per-user earliest views and aggregating frequencies using SQL or Python.
A streaming platform wants to find the movie that most customers watched first in their viewing history. Given the MOVIE_VIEWS table, write an SQL query to determine which movie title appears most frequently as users' earliest-dated movie view (i.e., the most popular first-watched film). If there is a tie, return all such titles with their counts.
Tables
MOVIE_VIEWS(customer_id INTEGER, title VARCHAR, date DATE)
Hints
- Rank or aggregate by the minimum date per customer, then count titles and order by frequency.
- Use DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY date) to identify each customer's earliest view; then group by title and select the maximum count (return all ties).