Friendly Movies Streamed Last Month - Problem
Imagine you're working for a streaming platform like Netflix, and you need to create a kid-friendly content report for June 2020. Your task is to analyze the TV programming data and extract all the movies that are suitable for children.
You have access to two important databases:
- TVProgram: Contains information about what content was streamed on which date and channel
- Content: Contains metadata about each piece of content including title, whether it's kid-friendly, and content type (movie, series, etc.)
Your goal is to find all distinct movie titles that meet these criteria:
- Were streamed during
June 2020 - Are marked as
kid-friendly(Kids_content = 'Y') - Are of type
'Movies'
This is a common business requirement for content platforms to generate family-friendly viewing reports and recommendations.
Input & Output
example_1.sql — Basic Example
$
Input:
TVProgram table:
| program_date | content_id | channel |
|--------------|------------|---------|
| 2020-06-01 | 1 | LC-Channel |
| 2020-05-30 | 2 | LC-Channel |
| 2020-06-15 | 3 | LC-Channel |
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 |
›
Output:
Empty result set (no kid-friendly movies in June 2020)
💡 Note:
Content 1 was streamed in June but is not kid-friendly (N). Content 2 is kid-friendly but wasn't streamed in June. Content 3 was streamed in June but is not kid-friendly and not a movie. Content 4 is kid-friendly movie but wasn't streamed in June.
example_2.sql — With Results
$
Input:
TVProgram table:
| program_date | content_id | channel |
|--------------|------------|---------|
| 2020-06-01 | 1 | Disney |
| 2020-06-15 | 1 | Disney |
| 2020-06-20 | 3 | Cartoon |
Content table:
| content_id | title | Kids_content | content_type |
|------------|------------|--------------|-------------|
| 1 | Toy Story | Y | Movies |
| 2 | Breaking Bad | N | Series |
| 3 | Finding Nemo | Y | Movies |
›
Output:
| title |
|--------------|
| Toy Story |
| Finding Nemo |
💡 Note:
Both 'Toy Story' and 'Finding Nemo' are kid-friendly movies that were streamed in June 2020. Note that 'Toy Story' was streamed twice but appears only once in the result due to DISTINCT.
example_3.sql — Edge Case
$
Input:
TVProgram table:
| program_date | content_id | channel |
|--------------|------------|---------|
| 2020-06-30 | 1 | Kids-TV |
| 2020-07-01 | 2 | Kids-TV |
Content table:
| content_id | title | Kids_content | content_type |
|------------|--------------|--------------|-------------|
| 1 | Inside Out | Y | Movies |
| 2 | Coco | Y | Movies |
›
Output:
| title |
|------------|
| Inside Out |
💡 Note:
Only 'Inside Out' qualifies because it was streamed on 2020-06-30 (last day of June). 'Coco' was streamed on 2020-07-01 which is July, not June.
Visualization
Tap to expand
Understanding the Visualization
1
Viewing History
Check what was watched in June 2020 from the TV program log
2
Content Database
Look up each item in the content catalog to get metadata
3
Apply Filters
Keep only items that are kid-friendly AND movies
4
Unique Titles
Remove duplicates to get distinct movie titles
Key Takeaway
🎯 Key Insight: Use JOIN operations to combine related data efficiently, then apply all filters in the WHERE clause for optimal performance
Time & Space Complexity
Time Complexity
O(n + m)
Single pass through both tables with efficient JOIN operation
✓ Linear Growth
Space Complexity
O(k)
Space only for the result set of distinct movie titles
✓ Linear Space
Constraints
- TVProgram table contains at most 104 records
- Content table contains at most 104 records
- All dates are in valid YYYY-MM-DD format
- Kids_content is either 'Y' or 'N'
- content_type can be 'Movies', 'Series', or other values
- Title lengths are between 1 and 50 characters
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code