Friendly Movies Streamed Last Month

Friendly Movies Streamed Last Month

What is this problem about?

The Friendly Movies Streamed Last Month coding problem is a SQL task. You are given a TVProgram table (with program dates) and a Content table (with content details like title and "Kids_content" flag). You need to write a query to report the distinct titles of the kid-friendly movies streamed in a specific month, such as June 2020.

Why is this asked in interviews?

Amazon uses this Database coding problem to test basic table joining and filtering. It’s an "Easy" difficulty question that evaluates your ability to link tables using primary/foreign keys, apply boolean filters (Kids_content = 'Y'), and filter by date ranges. It is a very standard reporting query expected of any data analyst or backend engineer.

Algorithmic pattern used

This problem relies on an INNER JOIN and Date Filtering pattern.

1. Join: INNER JOIN the TVProgram table and the Content table on the content_id.
2. Filter Content: Add a WHERE clause condition for Kids_content = 'Y' and content_type = 'Movies'.
3. Filter Date: Add a condition to restrict the program_date to the target month (e.g., using YEAR() and MONTH() functions or a string LIKE match depending on the format).
4. Distinct: Use DISTINCT title to ensure movies broadcasted multiple times are only listed once.

Example explanation

TVProgram: (1, "2020-06-10"), (2, "2020-05-15") Content: (1, "Finding Nemo", "Y", "Movies"), (2, "Die Hard", "N", "Movies")

1. Join matches ID 1 with "Finding Nemo" and ID 2 with "Die Hard".
2. Filter for Kids = 'Y' leaves only "Finding Nemo".
3. Filter for June 2020 keeps "Finding Nemo". Result: "Finding Nemo".

Common mistakes candidates make

• Missing DISTINCT: Forgetting that a movie could be streamed twice in the same month, which would cause duplicate rows in the output without DISTINCT.
• Date Formatting: Incorrectly filtering the date. Using program_date BETWEEN '2020-06-01' AND '2020-06-30' is usually the safest and most index-friendly method compared to using LIKE '%-06-%'.
• Ignoring Content Type: Forgetting to filter for content_type = 'Movies', as the table might contain TV shows as well.

Interview preparation tip

Always check for multiple filtering conditions in the prompt. Interviewers often throw in extra details (like "Movies" vs "Shows" AND "Kids" vs "Adults") to see if you read the requirements carefully.