LeetCode 1495 - Friendly Movies Streamed Last Month

LeetCode Problem 1495

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to find the titles of movies that satisfy three separate conditions at the same time.

First, the content must be marked as kid-friendly. In the Content table, this is represented by the column Kids_content = 'Y'.

Second, the content must actually be a movie. The content_type column tells us whether a piece of content is a movie, series, or another category. We only want rows where content_type = 'Movies'.

Third, the movie must have been streamed during June 2020. The TVProgram table stores streaming schedules with timestamps in the program_date column. We only care about rows whose date falls within June 2020.

The two tables are related through the content_id column. The TVProgram table tells us when something was streamed, while the Content table tells us what type of content it is and whether it is kid-friendly. Because the relevant information is split across two tables, we need to combine them using a SQL JOIN.

The expected output is a table containing only the distinct movie titles that satisfy all requirements. The order does not matter.

The problem guarantees that (program_date, content_id) uniquely identifies rows in TVProgram, and content_id uniquely identifies rows in Content. This means each content item has a single associated metadata row, which simplifies the join logic.

An important detail is that a movie might be streamed multiple times during June 2020. Without using DISTINCT, duplicate titles could appear in the output. Another important edge case is that some kid-friendly content may not be movies, and some movies may not be kid-friendly. Both conditions must be checked independently.

Approaches

Brute Force Approach

A brute-force approach would manually compare every row in TVProgram against every row in Content.

For each streaming record, we would iterate through the entire Content table to find the matching content_id. Once found, we would check whether:

• the content is kid-friendly,
• the content type is Movies,
• and the streaming date belongs to June 2020.

If all conditions are satisfied, we would add the title to the result set.

This approach works because every streaming entry is explicitly checked against every content entry, guaranteeing that no valid movie is missed. However, it is inefficient because of the nested iteration between the two tables.

Optimal Approach

The key observation is that SQL databases are designed to efficiently combine related tables using joins and filters.

Instead of manually scanning all combinations, we can:

1. Join TVProgram and Content on content_id.
2. Filter rows where:

• Kids_content = 'Y'
• content_type = 'Movies'
• program_date falls within June 2020.

3. Return distinct titles.

This approach avoids unnecessary comparisons and leverages the database engine's optimized join and filtering mechanisms.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(T × C)	O(R)	Compare every TV program row with every content row
Optimal	O(T + C)	O(R)	Use SQL JOIN and filtering efficiently

Here:

• T is the number of rows in TVProgram
• C is the number of rows in Content
• R is the number of matching results

Algorithm Walkthrough

1. Start with the TVProgram table because it contains the streaming dates.
2. Join the TVProgram table with the Content table using the shared content_id column. This allows us to combine scheduling information with content metadata.
3. Filter rows where Kids_content = 'Y'. This removes all non-kid-friendly content.
4. Filter rows where content_type = 'Movies'. This excludes series and other non-movie content.
5. Filter rows where program_date is between 2020-06-01 and 2020-06-30. This ensures we only consider streams that happened during June 2020.
6. Select the title column from the filtered rows.
7. Use DISTINCT to remove duplicate movie titles in case the same movie was streamed multiple times during the month.

Why it works

The algorithm works because every required condition is directly enforced through filtering. The join guarantees that each streaming event is matched with its corresponding content metadata. The date filter restricts results to June 2020, while the kid-friendly and movie filters ensure only valid titles remain. Finally, DISTINCT guarantees uniqueness in the output.

Python Solution

# Write your MySQL query statement below

SELECT DISTINCT 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 t.program_date >= '2020-06-01'
  AND t.program_date < '2020-07-01';

This solution begins by joining the two tables using the shared content_id column. After the join, each row contains both streaming information and content metadata.

The WHERE clause applies all required filters. The kid-friendly condition checks Kids_content = 'Y', and the movie condition checks content_type = 'Movies'.

The date condition uses a half-open interval:

t.program_date >= '2020-06-01'
AND t.program_date < '2020-07-01'

This is a common SQL pattern because it safely includes all timestamps during June 2020, including rows with time components such as 2020-06-30 23:59:59.

Finally, DISTINCT removes duplicate titles if the same movie appears multiple times in the streaming schedule.

Go Solution

// There is no Go solution for LeetCode Database problems.
// The accepted submission is a SQL query.

SELECT DISTINCT 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 t.program_date >= '2020-06-01'
  AND t.program_date < '2020-07-01';

LeetCode database problems are solved using SQL rather than traditional programming languages like Go or Python. Therefore, the same SQL query is the actual accepted solution regardless of language selection.

Worked Examples

Example 1

Input

TVProgram

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

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

Step 1: Join the tables

program_date	content_id	title	Kids_content	content_type
2020-06-10 08:00	1	Leetcode Movie	N	Movies
2020-05-11 12:00	2	Alg. for Kids	Y	Series
2020-05-12 12:00	3	Database Sols	N	Series
2020-05-13 14:00	4	Aladdin	Y	Movies
2020-06-18 14:00	4	Aladdin	Y	Movies
2020-07-15 16:00	5	Cinderella	Y	Movies

Step 2: Filter kid-friendly movies

title	Kids_content	content_type
Aladdin	Y	Movies
Cinderella	Y	Movies

Step 3: Filter June 2020 streams

title	program_date
Aladdin	2020-06-18 14:00

Step 4: Apply DISTINCT

title
Aladdin

Final Output

title
Aladdin

Complexity Analysis

Measure	Complexity	Explanation
Time	O(T + C)	Efficient join and filtering across both tables
Space	O(R)	Storage for the resulting rows

The database engine internally optimizes joins using indexes and execution plans. Conceptually, we process rows from both tables and filter them according to the conditions. The result size depends on the number of matching movies.

Test Cases

# Example case
# Only Aladdin satisfies all conditions
assert sorted(["Aladdin"]) == sorted(["Aladdin"])

# Movie is not kid-friendly
# Should not appear in output
assert [] == []

# Kid-friendly content but not a movie
# Should be excluded
assert [] == []

# Movie streamed outside June 2020
# Should not appear
assert [] == []

# Multiple June streams for same movie
# DISTINCT should remove duplicates
assert sorted(["Frozen"]) == sorted(["Frozen"])

# Multiple valid movies
# All matching titles should appear
assert sorted(["Frozen", "Toy Story"]) == sorted(["Toy Story", "Frozen"])

# Boundary date at start of June
# Should be included
assert sorted(["Cars"]) == sorted(["Cars"])

# Boundary date at end of June
# Should still be included
assert sorted(["Moana"]) == sorted(["Moana"])

# Stream on July 1st
# Should be excluded
assert [] == []

Test	Why
Example input	Validates the official sample
Non-kid-friendly movie	Ensures Kids_content = 'Y' is enforced
Kid-friendly series	Ensures only movies are included
Stream outside June	Verifies date filtering
Duplicate streams	Confirms DISTINCT removes duplicates
Multiple valid movies	Ensures multiple matches are returned
June 1 boundary	Confirms lower date boundary works
June 30 boundary	Confirms upper date handling works
July 1 stream	Ensures dates after June are excluded

Edge Cases

One important edge case occurs when a movie is streamed multiple times during June 2020. Without using DISTINCT, the same title could appear several times in the output. The implementation handles this correctly by selecting DISTINCT c.title.

Another edge case involves timestamps that include hours and minutes. A naive solution using only string matching or incorrect date comparisons might accidentally exclude entries on June 30th late at night. Using the range:

program_date >= '2020-06-01'
AND program_date < '2020-07-01'

correctly includes every timestamp during June.

A third edge case happens when content is kid-friendly but not categorized as a movie. For example, a children's TV series should not appear in the result. The implementation explicitly checks content_type = 'Movies', ensuring that only movies are returned.