Friendly Movies Streamed Last Month - Problem

You are given two tables: TVProgram and Content.

The TVProgram table contains information about TV programs:

  • program_date: The date when the program was aired
  • content_id: The ID of the content
  • channel: The TV channel name

The Content table contains details about each content:

  • content_id: Unique identifier for the content
  • title: The title of the content
  • Kids_content: Whether it's kid-friendly ('Y') or not ('N')
  • content_type: Type of content (movies, series, etc.)

Write a SQL query to find the distinct titles of kid-friendly movies that were streamed in June 2020.

Table Schema

TVProgram
Column Name Type Description
program_date PK date Date when the program was aired
content_id PK int ID of the content (foreign key to Content table)
channel varchar TV channel name
Primary Key: (program_date, content_id)
Content
Column Name Type Description
content_id PK varchar Unique identifier for the content
title varchar Title of the content
Kids_content enum Whether content is for kids: 'Y' (yes) or 'N' (no)
content_type varchar Type of content (movies, series, etc.)
Primary Key: content_id

Input & Output

Example 1 — Kid-friendly Movies in June 2020
Input Tables:
TVProgram
program_date content_id channel
2020-06-10 1 Disney Channel
2020-06-15 2 Nickelodeon
2020-07-01 3 HBO
Content
content_id title Kids_content content_type
1 Moana Y Movies
2 Frozen Y Movies
3 The Dark Knight N Movies
Output:
title
Moana
Frozen
💡 Note:

From the TVProgram table, we find programs aired in June 2020 (2020-06-10 and 2020-06-15). Joining with Content table, both Moana and Frozen are kid-friendly movies (Kids_content = 'Y' and content_type = 'Movies'). The program on 2020-07-01 is excluded as it's in July.
Example 2 — Mixed Content Types
Input Tables:
TVProgram
program_date content_id channel
2020-06-05 4 Cartoon Network
2020-06-20 5 Disney XD
Content
content_id title Kids_content content_type
4 Tom and Jerry Y Series
5 Toy Story Y Movies
Output:
title
Toy Story
💡 Note:

Both programs aired in June 2020 and are kid-friendly, but only 'Toy Story' is a movie. 'Tom and Jerry' is excluded because it's a series, not a movie.
Example 3 — No Results
Input Tables:
TVProgram
program_date content_id channel
2020-06-12 6 Adult Swim
Content
content_id title Kids_content content_type
6 Deadpool N Movies
Output:
title
💡 Note:

Although 'Deadpool' is a movie that aired in June 2020, it's not kid-friendly (Kids_content = 'N'), so no results are returned.

Constraints

  • program_date is in format YYYY-MM-DD
  • Kids_content is either 'Y' or 'N'
  • content_type includes 'Movies', 'Series', etc.
  • Multiple programs can have the same content_id on different dates

Visualization

Tap to expand
Kid-friendly Movies Streamed in June 2020TVProgram Tableprogram_datecontent_idchannel2020-06-101Disney2020-06-152NickContent Tablecontent_idtitleKids_contentcontent_type1MoanaYMovies2FrozenYMoviesSQL Operations1. INNER JOIN ON content_id2. WHERE program_date in June 20203. AND content_type = 'Movies' AND Kids_content = 'Y'ResulttitleMoanaFrozenOnly kid-friendly movies from June 2020 are returned
Understanding the Visualization
1
Input Tables
TVProgram with dates and Content with details
2
INNER JOIN
Combine tables on content_id
3
Filter & Select
Apply conditions and get distinct titles
Key Takeaway
🎯 Key Insight: Use INNER JOIN to combine related tables and apply multiple WHERE conditions to filter data precisely

Related Problems

Asked in
Netflix 15 Disney 12 Amazon 8
25.4K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen