Table: TVProgram
+---------------+---------+ | Column Name | Type | +---------------+---------+ | program_date | date | | content_id | int | | channel | varchar | +---------------+---------+ (program_date, content_id) is the primary key (combination of columns with unique values) for this table. This table contains information of the programs on the TV. content_id is the id of the program in some channel on the TV.
Table: Content
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| content_id | varchar |
| title | varchar |
| Kids_content | enum |
| content_type | varchar |
+------------------+---------+
content_id is the primary key (column with unique values) for this table.
Kids_content is an ENUM (category) of types ('Y', 'N') where:
'Y' means is content for kids otherwise 'N' is not content for kids.
content_type is the category of the content as movies, series, etc.
Write a solution to report the distinct titles of the kid-friendly movies streamed in June 2020.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: TVProgram table: +--------------------+--------------+-------------+ | program_date | content_id | channel | +--------------------+--------------+-------------+ | 2020-06-10 08:00 | 1 | LC-Channel | | 2020-05-11 12:00 | 2 | LC-Channel | | 2020-05-12 12:00 | 3 | LC-Channel | | 2020-05-13 14:00 | 4 | Disney Ch | | 2020-06-18 14:00 | 4 | Disney Ch | | 2020-07-15 16:00 | 5 | Disney Ch | +--------------------+--------------+-------------+ Content table: +------------+----------------+---------------+---------------+ | content_id | title | Kids_content | content_type | +------------+----------------+---------------+---------------+ | 1 | Leetcode Movie | N | Movies | | 2 | Alg. for Kids | Y | Series | | 3 | Database Sols | N | Series | | 4 | Aladdin | Y | Movies | | 5 | Cinderella | Y | Movies | +------------+----------------+---------------+---------------+ Output: +--------------+ | title | +--------------+ | Aladdin | +--------------+ Explanation: "Leetcode Movie" is not a content for kids. "Alg. for Kids" is not a movie. "Database Sols" is not a movie "Alladin" is a movie, content for kids and was streamed in June 2020. "Cinderella" was not streamed in June 2020.
Problem Overview: The database stores streaming schedules in TVProgram and content metadata in Content. You need to return the titles of movies that are marked as kid‑friendly and were streamed during June 2020. The solution requires combining the two tables and filtering rows based on content attributes and stream date.
Approach 1: Equi-Join + Conditional Filtering (O(n) time, O(1) space)
The most direct solution joins the TVProgram and Content tables using an equi-join on content_id. After joining, filter the rows where Kids_content = 'Y' and content_type = 'Movies'. Then restrict the stream date to June 2020 using MONTH(program_date) = 6 and YEAR(program_date) = 2020. Finally, select the movie title. The key insight is that the streaming schedule and content metadata live in different tables, so the join is required before applying the business rules.
This approach works because the join links each scheduled program entry with its content attributes. Once combined, simple conditional filters reduce the dataset to only kid‑friendly movies streamed in the specified month. SQL engines typically execute this efficiently using indexed lookups or sequential scans depending on schema design. The overall complexity is O(n) time where n is the number of rows scanned across the joined tables, with O(1) additional space since the database processes rows in place.
Example MySQL query:
SELECT c.title
FROM TVProgram t
JOIN Content c ON t.content_id = c.content_id
WHERE c.Kids_content = 'Y'
AND c.content_type = 'Movies'
AND MONTH(t.program_date) = 6
AND YEAR(t.program_date) = 2020;
This problem mainly tests your understanding of database querying, especially combining datasets using SQL joins and applying conditional filters with date functions in SQL. The challenge is not algorithmic complexity but correctly interpreting the schema and translating business requirements into SQL conditions.
Recommended for interviews: The equi-join with conditional filtering is the expected solution. Interviewers want to see that you immediately recognize the need for a join between the schedule table and the content metadata table. A correct join plus clear filtering conditions demonstrates solid SQL fundamentals and the ability to translate requirements into a single efficient query.
We can first use an equi-join to join the two tables based on the content_id field, and then use conditional filtering to select the child-friendly movies that were played in June 2020.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Equi-Join + Conditional Filtering | O(n) | O(1) | Best general solution when data is split across related tables and filtering conditions apply after joining. |
| Subquery with Filtering | O(n) | O(1) | Useful when filtering content first in a subquery, then selecting matching stream records. |
LeetCode 1495 "Friendly Movies Streamed Last Month" Amazon Interview SQL Question with Explanation • Everyday Data Science • 1,361 views views
Watch 1 more video solutions →Practice Friendly Movies Streamed Last Month with our built-in code editor and test cases.
Practice on FleetCode