Welcome to Subscribe On Youtube
1495. Friendly Movies Streamed Last Month
Description
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.
Solutions
Solution 1: Equi-Join + Conditional Filtering
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.
-
# Write your MySQL query statement below SELECT DISTINCT title FROM TVProgram JOIN Content USING (content_id) WHERE DATE_FORMAT(program_date, '%Y%m') = '202006' AND kids_content = 'Y' AND content_type = 'Movies';